![]() Hold Ctrl then click on the Customer column and then the Product column.You should see a small 1 next to Customer and a small 2 next to Product. Hold Ctrl then click on the Customer column and then the Product column. Now we can select which columns our merge query will join on. ![]() This will mean all rows in Table_A and all rows in Table_B will be shown in the resulting table. Select Full Outer (all rows from both) for the Join Kind.Choose Combine Queries then Merge from the menu.Press the Get Data button from the Get & Transform Data section.You will need to use the Add aggregation button to add the second metric. Add a descriptive column name, select Sum as the Operation for both the Quantity and Amount columns.You will need to use the Add grouping button to add a second dimension. Select Customer first and then Product second.Select Advanced to create a Group By query which groups by more than one dimension.Press the From Table / Range button in the Get & Transform section.Select a cell in table A or select the whole table.The data sets in this example have already been turned into tables named Table_A and Table_B, but you can read about how to make a table here.įirst let’s select our table and make a query. When you add data you won’t need to update the range in your queries as they will reference the name. This way, your queries can reference a table name instead of a range. TIP: I always find it’s a good idea to use Excel Tables with your data. We ideally want only 1 row of data for Bob and Sweaters and 1 row of data for Jane and T-Shirt. If you look at table A, you will notice that Bob and Jane have rows of data that will need to be aggregated. Aggregating The Tablesįirst we will need to aggregate the data to the customer and product level so that we can compare the two tables. ![]() If you’re running Excel 2016 then it’s already installed and can be found in the Data tab of the ribbon. Find out how to install Power Query here. Get & Transform was previously called Power Query in Excel 20, and you will need to install is as an add-in. Get & Transform will allow us aggregate these tables to the same level of granularity and join the aggregated data by their common dimensions to easily find the differences. There are several other differences in the tables, but spotting them manually will be hard and won’t scale when your tables have more data.
0 Comments
Leave a Reply. |