Related topics
Get to know Power Query
Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
Get to know Power Query

Merge queries and join tables

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

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.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!