This introduction tutorial for Power Query using OdataLink explains the most common transformation steps that can be done in Power Query (used by Power BI and Excel).
Whether integrating with MYOB or Xero, at the end of this tutorial, you will have learned how to:
Introduction tutorial video for Power Query
Renaming Steps
Each action you perform within Power Query creates a step. Power Query will assign a default name to these steps.
For large complex queries, we recommend you rename your steps to ensure that you can audit and track what is actually happening within your query. This is especially important if you use the same steps multiple times.
To rename a step within Power Query:
- Right-click on the step name in the panel on the right-hand side.
- Select Rename Step from the contextual menu.
- Enter the new name.
Ordering Steps
As you work through your the different actions within Power Query, you may realise that some steps would be more efficient in different order.
As an example, we recommend you always place filtering and ordering steps early in the list. Doing so ensures that Power Query will send the filtering and ordering instruction to OdataLink which ensures it is performed on the MYOB or Xero side instead of within Power Query.
To reorder steps within Power Query:
- Click and hold on a step name in the panel on the right-hand side.
- Drag and drop it in the new location.
Alternatively, you may also:
- Right-click on the step name in the panel on the right-hand side.
- Choose Move Before or Move After.
Filtering Data
You can filter the data within Power Query to optimise your report or dashboard. In addition, filters placed early in the process will ensure filtering is performed within MYOB or Xero.
To filter your data within Power Query:
- Find the column you want to filter.
- Click the dropdown next to the column name.
- Tick the values you want to include.
Sorting Data
You can sort the data within Power Query. Sorting your data is not as relevant for Power BI or Excel dashboard that uses charts extensively, but it is useful if displaying tables of information.
To sort your data within Power Query:
- Find the column you want to sort.
- Click the dropdown next to the column name.
- Choose whether you want to Sort Ascending or Sort Descending.
Ordering Columns
You can rearrange the order of columns within Power Query. This is useful while auditing your Power Query or when merging different sets of data together.
To order columns within Power Query:
- Select the column you want to order.
- Drag and drop it to it’s new position.
Removing Columns
You can remove columns within Power Query. This is extremely useful for:
- Reducing the amount of memory and space used by power query
- Removing fields that are used for temporary calculation or manipulation
- Removing fields that could confuse the user that creates the dashboards, charts and reports.
To remove columns within Power Query:
- Select the column you want to remove.
- Press DELETE on your keyboard.
Alternatively, you may also:
- Select the column you want to remove.
- Right-click on the column name.
- Select Remove from the contextual menu.
Expanding Columns
Most columns of data within Power Query will contain discrete values (e.g. an amount, a date, some text).
However, there are some specialised fields that are either records or tables.
Record fields are specialised fields that contains other fields. For instance, an Account field could contain an Account Number and Account Name fields.
Table fields are specialised fields that contains multiple records. For instance, on an invoice endpoint, a Lines field could contain multiple transaction lines. In turn, each transaction line could be made up of other fields such as Account, Job, Tracking Categories, Amounts, etc.
To get to the underlying fields within these specialised fields, you can:
- Click on the dropdown next to the column name.
- Tick the fields you want to include and add to your power query.
Expanding Record fields will simply add the sub fields to your data set.
Expanding Table Fields, on the other hand will not only add the sub fields to your data set, but it may also duplicate every record you previously had. This is because you may have multiple records within each table. When you expand Table Fields, Power Query will repeat the values of the original columns for each record in your expanded table.
This means amounts of measures of the original table should not be used. Otherwise, you will duplicate the values and amounts on your reports and charts.
Adding Columns
You may also add your own custom columns to Power Query. This is useful in order to add calculated fields.
To add a custom column:
- Select Add Column in the Power Query Ribbon.
- Click on Add Custom Column.
- Enter a name for your new column.
- Enter a formula.
- Click ok to add the column.