I recently downloaded an ebook from “Power Query Training” https://www.powerquery.training/magic-tricks-data-wizards/ which I am currently going through. The first chapter deals with merging tables using Excel’s power query so I thought I would show you how to do the same exercise using Power BI.
Having opened Power BI the first thing we want to do is to connect to our data source. In this case we select Excel and then press the yellow connect button.
Once you have selected your file you will see all the sheets you can connect to. It’s a good idea to select each sheet individually first to check the data is correct before proceeding. Once all the tables you want are select click the edit button.
Once the data is in Power BI you want to select the Employee2 table and then select “Merge Queries”. Here you have a choice between creating a merge using the existing query or create a merge using a new query. If you want to keep the data that you have just brought into BI intact, then select “Merge Queries As New”.
Having selected the merge queries button a new merge window pops up. The Employee2 table is already selected. Now select the BusinessEntity1 table. Now go back to the Employee2 table and select TerritoryID. Then in the table below select the TerritoryID. This will join both tables on these columns. Notice a Left Out join is selected in the “Join Kind” at the bottom.
Next, select BusinessEntityID columns from both tables. Press the OK button when done.
Change the join type from Left Outer join to Right Outer join and then press OK.
Click on the double arrow at the top of the Sales column. In the drop down box select “Aggregate”.
Next select “Sum of OrderQty” and “Sum of LineTotal”. Press OK.
Select the BusinessEntity1 column. This time we want to have the “Expand” radio button selected. Deselect everything except the “RegionBE” field. Click OK.
Make sure the SalesLineTotal is also sorted by ascending order.
Rename the new columns to make them more understandable to users.
Now when we look at our new table in the table view you can see all the Regions are grouped with sales in descending order.