Why Is My Merge Not Working in PowerQuery?
A Summary of Merging in PowerQuery (Excel or Power BI )
Merging in PowerQuery, whether in Excel or Power BI, is one of the core skils for your average data transformationalist (is that a word?!) Think automated VLOOKUPs but with a fundamental difference (see here for a bit more on that).
In essence, the purpose of a merge is to bring together two datasets so that the information of both datasets is joined in a single table. To make sure the correct information is on each row in our new table, a column (or multiple columns) of data, common to both datasets, is used as a ‘key’ to match the correct rows together.
The 3 things to check when your merge isn't working
From our recap above then, a merge is pretty like a VLOOKUP… but what happens when the ‘key’ column doesn’t appear to do the matching process correctly even though you’re sure that the data in the key column is the same in each dataset? Instead of getting matching rows you might get either:
a) An error message or
b) the merged columns show nulls
When you come across this problem, there are three things that you need to check:
1) The case of any text in the key column
2) The datatype of values in the key column
3) The dreaded trailing space in the key columns
Read on for more information on these checks as well as details on how to fix these issues.
Resource Listing
The Video
- 00:26 - Summary of the merge function
- 00:58 - How a merge might not work as expected
- 01:30 - Demonstration of a correct merge
- 02:58 - How a mismatch in cases prevents a correct merge and how to fix it
- 07:02 - How a mismatch in data type prevents a correct merge and how to fix it
- 09:29 - How trailing spaces prevents a correct merge and how to it
Check 1 - A Mismatch of Cases in the Key Column
A really important thing to remember about PowerQuery is that it is case sensitive. This means that any text characters in your key columns HAVE to be the same case in both key columns for the merge to work. This is NOT like a VLOOKUP, where the case does not need to be the same in the two key columns. Let’s take a PowerQuery example:
a) Let’s say in our key column in Dataset 1, that all values start with the prefix ‘PO’.
b) Whereas in the key column in Dataset 2, that all values start with the prefix ‘po’.
In this example, a merge will be performed, but instead of bringing in matching values, the merge will return null values in the columns that have been merged in. This is because “PO” is in upper case in the first dataset and lower case in the other. As PowerQuery is case sensitive, as far as it is concerned, the two values are completely different.
How to fix this issue:
To resolve this issue, we simply need to change the case of the key column of one of the datasets.
Let’s say we are merging Dataset 2 into Dataset 1 – there are two options on what to do.
In Dataset 2:
a) Right click the key column header,
b) then select Transform from the resulting menu,
c) followed by Upper Case.
Alternatively, left click the key column header, select the Transform tab, then ‘Format’, then Upper Case.
This will change ‘po’ to ‘PO’ which matches the text value in the first dataset.
If you want to make the change in Dataset 1 rather than Dataset 2, BEFORE the Merge step, perform the same steps as above but select Lower Case instead of Upper Case. This will lower the case from ‘PO’ to ‘po’.
Check 2 - Checking Data Type
Ah data types. Data types exist in Excel too (think ‘General’ or ‘Text’ or ‘Short Date’) but they’re much more important in PowerQuery.
PowerQuery cannot use two columns that are different data types as key columns – the data types must be the same. How can you tell this?
a) Look in the headers of the columns that you’re trying to use as a key. Each column has an icon in the top left hand corner like a ‘123’ or ‘ABC’ or a calendar icon. These denote the data type of each column. These icons need to be the same for the merge to work.
b) Alternatively, when you try and perform the merge and select your key columns in the merge menu, PowerQuery will present a warning sign at the bottom of the merge menu saying that it can’t merge datasets using key columns of different data types.
How to fix this issue:
This is a simple one to fix – simply change the datatype of one of your key columns so that it matches the other!
To do this,
a) Select your key column where you want to change the datatype
b) On the Transform tab, go to the Any Column section of the ribbon,
c) Find the Data Type section of the ribbon and select the desired data type
Check 3 - Check for trailing spaces
This is the problem that gets everyone, the first time they come across it – if it’s had you stumped, don’t feel down as you’re in good company.
Sometimes data values have trailing spaces at the end of values. It’s really hard to pick these up just by looking at the values as you can’t see them and so generally you won’t find out there’s a problem until the merge pulls up some null values.
So what is a trailing space?
Once again let’s imagine we’re trying to merge together two datasets.
a) In the key column of one dataset, in 1 row there is the value ‘PO123’
b) In the key column dataset 2, in (what should be!) the matching row, we have the value ‘PO123 ‘
You can see (thanks to the apostrophe…which of course you won’t have in PowerQuery!) that there are three spaces after the value ‘3’ in the second dataset. These are trailing spaces and as PowerQuery is character sensitive, this is enough to prevent the merge as the values in the key columns do not match exactly.
How to fix this issue:
First you need to find which key column has the trailing spaces in it. To do this, navigate to the key column and select a cell within it. The value in this cell will come up at the bottom of the PowerQuery editor. If you then left click at the end of the value string, the trailing spaces (if they exist) will come up.
Once you’ve located the correct key column then we need to remove the trailing spaces. To do this:
a) Highlight the key column
b) In the Transform Tab select the Format dropdown
c) Select Trim.
.... and a little more!
Refreshing a merge – sometimes you make changes to the applied steps of a query above a merge, that then end up causing the merge to fail. Changing data types is key for this one.
If you want find out of a change you have made to a previous step will rectify the problem with the merge, go to the merge step, click on the cog, and in the resulting merge menu, click on the refresh button in the top right hand corner.