Power BI – Query Formulas

Power BI is a powerful business analytics tool by Microsoft that helps in analyzing data and sharing insights. One of the most powerful features of Power BI is Query Formulas. Query Formulas are essentially used to transform and shape data that is imported into Power BI.

Here are some important things to know about Query Formulas in Power BI:

• Query Formulas are created using the M language, which is a functional programming language used for querying and transforming data.
• The M language is similar to Excel formulas, but it has many additional functions that can be used to manipulate data.
• Query Formulas can be created by using the “Add Column” or “Add Measure” buttons in the Query Editor.
• Query Formulas can be used to perform various data transformation tasks such as merging tables, splitting columns, and filtering data.
• Query Formulas can be reused across different reports and dashboards in Power BI.
• Query Formulas can be combined with DAX formulas (Data Analysis Expressions) to create more complex calculations and data models.
• Query Formulas can be used to create calculated columns, which are columns that are calculated based on a formula, and calculated tables, which are tables that are created based on a formula.

In summary, Query Formulas in Power BI is a powerful tool for data transformation and shaping. They are created using the M language and can be used to perform various data manipulation tasks. They can be combined with DAX formulas to create more complex calculations and data models.

The Power Query Editor offers the ability to modify the automatically generated steps as well as create custom formulas for more precise data manipulation. The formula associated with each action taken on the data is displayed in the formula bar, which can be accessed by selecting the View tab on the ribbon and then choosing Formula Bar.

All the applied steps in a query are stored as text in the Power Query Editor, which can be viewed or modified as needed. To access the Advanced Editor and view or modify the text for a query, simply select View and then choose Advanced Editor.

Displayed below is a screenshot of the Advanced Editor, showing the query steps for the USA_StudentEnrollment query, which are formulated using the Power Query Formula Language, commonly known as M.

Create Power Query Formula in Excel

Creating Power Query formulas in Excel involves using the Power Query Editor to transform and shape data. Here are the steps to create Power Query formulas in Excel:

• Select the data range in Excel that you want to work with.
• Go to the Data tab on the ribbon, and select the “From Table/Range” button to open the Power Query Editor.
• In the Power Query Editor, use the available transformation tools and functions to clean and manipulate the data to your desired format.
• To create a custom formula, select the “Add Column” button and choose “Custom Column” from the drop-down list.
• In the Custom Column dialog box, enter a name for the new column and write the formula in the “Formula” field using the M language.
• Once you have written the formula, select OK to create the new column.
• To view the results of the formula, select the “Close & Load” button on the Home tab of the Power Query Editor.
• The new data with the applied transformations and the custom formulas will now be loaded into Excel.

Creating Power Query formulas in Excel enables users to transform and shape data according to their specific needs, allowing for more effective data analysis and reporting.