M365 Excel - Merge queries and join tables

Currently, data is only summarized at the product level. In the Category table, products can be rolled up a level. so, you can load the Category table, and create a join on the Product Name fields.

1. Select the Categories worksheet, and then then select Data > Get & Transform data > From Table or Range.

2. Select Close & Load the table to return to the worksheet, and then rename the Sheet tab to "PQ Categories".

3. Select the Sales Data worksheet, open Power Query, and then select Home > Combine > Merge Queries > Merge as New.

4. In the Merge dialog box, under the Sales table, select Product Name column from the drop-down list.

5. Under the Product Name column, select the Category table from the drop-down list.

6. To complete the join operation, select OK.
   
   Power Query returns the Sales data. But you need to tell Power Query which table column you want to display.

7. To specify the master category, select Field List Filter > Category, and then select OK.
   
   Power Query displays the combined tables.

8. Move the Category column from the right-hand side.

9. Remove the table name and the column title.

10. Select Home > Close and Load.
    
    Power Query creates a new worksheet for you.

11. Rename the sheet to "PQ Merge".
    
    Note   There’s a new query in the Query and Connections dialog pane, but the previous transformations remain. This is useful when you want to create multiple views of the same data to create different reports.

12. To make the query title more descriptive, hover over the query name, select the ellipses from the Query dialog box, select the Properties setting, and then change it to Merge Tables.