This past weekend I attended SQL Saturday in Portland, OR. While I was there, I attended Reza Rad’s session on Advanced Data Transformations with Power Query. During that session, Reza showed a cool trick to merge data based on two columns through the user interface… without concatenating the columns first.
Assume for a second that we have data that looks like this:
There’s two tables, and we want to join the account name to the transaction. The problem is that the unique key to join these two tables (which isn’t super obvious here) is a combination of the Acct and Dept fields. (Elsewhere in the data the same account exists in multiple departments.
To get started, I created two connection only queries, one to each table.
Power query merge two tables based on the transaction date between two dates. Is it possible to do this in Excel Power Query? Follow edited Jun 11 '20 at 21:13. 33k 5 5 gold badges 29 29 silver badges 54 54 bronze badges. Power Query can be really useful when you want to merge different Excel tables.Now I am not talking about tables with the same structure. With Power Query, y. With Power Query (called Get & Transform Data in previous Excel versions), you can import or connect to external data, and then shape that data, for example remove a column, change a data type, or merge tables, in ways that meet your needs. Then, you can load your query into Excel to. Apr 20, 2020 Returns a table that is the result of merging a list of tables, tables. The resulting table will have a row type structure defined by columns or by a union of the input types if columns is not specified. Example 1 Merge the three tables together.
In this tutorial, you can use Power Query's Query Editor to import data from a local Excel file that contains product information and from an OData feed that contains product order information. You perform transformation and aggregation steps, and combine data from both sources to produce a 'Total Sales per Product and Year' report.
Power Query Excel Merge Tables Free
- Select a cell in the left table (Transactions) –> create a new query –> From Table –> Close & Load To… Connection only
- Select a cell in the right table (COA) –> create a new query –> From Table –> Close & Load To… Connection only
My Original Approach
Now, with both of those created, I want to merge the data so I get the account name on each row of the Transactions table. So how…?
Originally I would have edited each query, selected the Acct and Dept columns, and merged the two columns together, probably separating them with a custom delimiter. (This can be done via the Merge command on the Transform or the Add Column tab.)
Essentially, by concatenating the columns, I end up with a single column that I can use to dictate the matches.
Excel Power Query Tutorial
Reza’s presentation showed that this isn’t actually necessary, and I don’t need to merge those columns at all…
So here’s how we can get those records from the COA Table into the Transactions table:
- Right click the Transactions query in the Workbook Queries pane
- Choose Merge
- Select the COA query
The data now looks like this, asking for us to select the column(s) we wish to use for the merge:
So here’s the secret:
- Under Transactions, click the Acct column
- Hold down the CTRL key
- Click the Dept column
And Power Query indicates the order of the columns you selected. It will essentially use this as a temporary concatenated value!
So now do the same to the COA table:
And then complete the merge. As you can see, you get a new column of data in your query:
of course, we can expand NewColumn to get just the Name field, and everything is working perfectly!
This is pretty cool, although not super discoverable. The really nice piece here is that it can save you the work of creating extra columns if you only need them to merge your data.
Excel Merge Tables Without Power Query
I should also mention that Reza showed this trick in Power BI Desktop, not Excel. But because it’s Power Query dealing with the data in both, it works in both. How cool is that?