1. Introduction #
Please note that Power Query is the same in both Excel and Power BI. The example below is made in Power BI Desktop , in Excel you can open Power Query via the following shortcut, and create a similar workflow:
2. Example from Power BI #
On the far left in Power BI we can see our data, which you can right-click on, rename, edit, etc.
To open the Power Query Editor, tap Edit Query .
This is what the Power Query Editor looks like.
Llilla is the data type ( decimal, text, number, date, etc. ), and can be changed by clicking on the field.
On the far right we have an overview of the changes we are making. If you select one, you can see the actual code in the text above, in this case changing column names. You can type these in manually or alternatively double-click / right-click and rename the columns marked in yellow.
You can remove the changes you have made by pressing X next to Rename columns in this case.
We can filter which rows we want to see from the wikipedia table, in this case under Country , as we only have sales data for Canda, France, Germany, Mexico and the United States of America. We can search for these by left-clicking on the icon on the far right of the column, and in the filter turn off everything, and the few we need.
Additionally, we can remove the columns that are not useful to us by right-clicking on them and selecting Remove .
We can also change a value in the row if needed. In this case, we change United States to United States of America to match the value from our sales data.
We can now merge our two queries, similar to a database relation. Under Merge Queries from our Sales data query, we can merge on Country, select our Wikipedia table further down, and merge on Country / Dependency, There will be a popup about Privacy Filter, here you can check Ignore and then OK and OK.
On the far right in our Sales data we have a new column with our query from Wikipedia. We can click on this and choose which columns we want to output, in this case Region .
Since our query is now complete, we can click Close & Apply in the top left corner.
