If you have never worked with Power Query before, check additional resources at the bottom of the page.
Power Query is a powerful data connection, cleaning, and shaping technology that can be used in Excel and Power BI alike. The content retrieved from external sources is transformed using Power Query M language, which offers a set of functions for working with tables, lists, dates, etc.
When adding steps using User Interface, Power Query Editor will automatically add and configure steps. You can edit them instead of adding new step correcting the results.
Update generated steps
Selecting columns
When selecting columns from a data source, the generated step will use Table.SelectColumns function. If the column you defined is missing, you will receive an error and the query will fail. Adding UseNull ensures that the query will execute successfully and the values in the missing column will be set to null.
Expanding columns
If you expand multiple columns, the new column names will be predefined:
Instead of renaming them in the following step, edit the Table.ExpandTableColumn formula.
Taking a sneak-peek
When working with lists or table column types, you may take a sneak-peek by clicking within the cell, next to the value. The data preview will be displayed at the bottom of the window.
Merging steps
When renaming multiple columns, the Power Query editor will automatically merge them into one step. In some cases, however, separate steps are added.
For example, if you want to replace multiple text occurrences using UI
multiple steps will be generated.
I like copying formula of the step above into the step below to merge text transformations into one step.
Step Replaced a with A:
Step Replaced b with B
I update the Replaced b with B to replace a reference to Replaced a with A with its formula:
After deleting the no longer needed step Replaced a with A and renaming step Replaced b with B, my transformations are much more readable.
Working with functions
In case you are applying the same transformation to multiple columns, you may want to save them as a function, instead of copying the formula over, and updating it in multiple steps.
Functions scoped to a query
To create a new private function, open Advanced Editor, declare a new function, define input parameters, and copy the transformations from the step. I personally like defining functions at the very beginning, above the source.
Update the function body to use parameters instead of hardcoded parameters
And replace the formula in the step
You will now see your function above the "Source" step. The navigation step is now renamed to an ID, but you can rename it.
Reusable functions
Functions defined in the scope of a Query are private for this query. To define global functions that can be reused, create a new blank query and paste the copied code to the formula.
Rename it to something representative of the actions it executes:
You can now use it in the query steps, just like the local functions you referenced before.
For more information about Power Query M functions, see Understanding Power Query M functions
Parameters
When referencing the step in the Power Query M functions, the data provided by previous steps is of type table.
For more details on accessing values of list or table items, see Item Access. If the column name is provided as parameter, you may use Record.Field (Record functions)
Passing current cell as parameter:
Passing current row as parameter using _:
Make sure that for any cells that may have null value, you define the corresponding parameter as nullable:
For more information about Power Query M types, see Types.
Best kept secret
To access all functions directly from your Power Query editor, create new blank query and in Advanced Editor type = #shared
The result:
After clicking next to the Function, you will see its documentation, along with usage example.
And clicking on the Function will allow you to test it:
Source: Data Preparation in Power BI By Reza Rad
Additional resources
- To get started using Power Query with Excel, see Automate data cleaning with Power Query module.
- For first steps in Power Query using Power BI, see Clean, transform, and load data in Power BI Clean, transform, and load data in Power BI
- Once you are familiar with the basics, have a look at [PowerQuery] My Best practices in Transform Data - it's a *great summary * of everything I wish I knew when I was starting with Power BI.
⭐ You will find the Power Query M functions used in my Power Platform cost optimization dashboard in the Power BI functions gist
Top comments (0)