Using Merge statements? Alex shows you how that can break the fold in PowerQuery! He's got you covered though and shows you a different way to approach it to make folding work! Using the UI!
Power Query provides a graphical user interface (UI) that allows you to use Merge statements to combine data from multiple tables into a single table. Here are the steps to use Merge statements in Power Query UI:
- Open Power Query Editor: Open the Excel workbook that contains the data you want to merge. On the Excel ribbon, click on the “Data” tab, then click “Get Data” and choose “From Other Sources” then “Blank Query” (for example).
- Connect to Data Sources: In the Power Query Editor window that opens, click on “Home” tab, then click on “Combine” and select “Merge Queries”. In the Merge dialog box that appears, you need to select the two data sources you want to merge.
- Select the Key Columns: Choose the columns that will be used as keys to join the two data sources. Click on the dropdown next to “Table/Query 1” and “Table/Query 2” to select the key columns from each table.
- Choose Join Type: You can choose different types of join operations (e.g., inner join, left join, right join, full outer join) to control how the data will be merged. You can do this by selecting the appropriate option from the dropdown menu in the Merge dialog box.
- Choose the Resultant Columns: Select the columns you want to keep from each table by checking the corresponding boxes in the “Columns to include in the merged table” section. You can also choose to rename columns, remove duplicate columns, or reorder the columns.
- Preview and Load: Once you have configured the Merge statement, you can click on the “Preview” button to see a preview of the merged table. If you are happy with the results, click on “Close & Load” to create a new table in your Excel workbook with the merged data.
That’s it! You have now successfully used Merge statements in Power Query UI to combine data from multiple tables into a single table.
More links on about Power Platform/Power BI
Feb 17, 2023 — In this article, you'll learn how to create queries with Power Query by discovering: How the "Get Data" experience works in Power Query. How to ...
Apr 8, 2022 — Hop into PowerQuery, where we force you to manipulate the data using a completely different language for some reason! So you get to learn ...
I have opened a Power BI report to make some changes. It has always worked... But if I edit a table definition, it falls down.
Jan 23, 2015 — It's a bit crazy that at this point I haven't posted anything clearly ... (A screenshot of the Power Query UI and how steps are created and ...