Problems with Merging two tables by UK Post Code

Power Query
1

Good Morning

I am trying to merge two data sets by UK Post Code in Power Query and the merge query will match ALL the 7 digit Uk Post Codes but return a null result if the post code is a 6 or 5 digit post code.

Please can you help me fix this problem

2

Hi @Ducati,

Provide a work in progress PBIX and XLSX data file including a mock up of the desired result, so members can better assist you.

3

Hi @Ducati, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

4

Good Morning. As the data I am working with is confidential patient information I am prohibited from uploading any of the files but I hope these three images will help demonstrate the problem

Output table showing the blank rows
Output table showing the blank rows424Ă—781 16.8 KB
Query showing Null returns
Query showing Null returns1744Ă—714 50 KB
Merge step in the query
Merge step in the query1008Ă—954 47.7 KB

I have looked everywhere but cant seem to find the solution so I am hoping someone on the forum can help me

5

As you can see the Merge works fine on any of the post codes that are 7 digits long but as soon as the post code is 6 or 5 digits then the query returns a null result

I even tried using a step join where I selected Post.1 first then Post then code and did the same sequence on the other table - but the result is always the same

if I match the Postcode 1 in the top table with Postcode in the second the result is still the same

Fuzzy match will not work either I tried that and it returns lots of duplicates which I remove but I loose about 5% of the rows which is not good enough

I also tried adding a 0 to the end of all the 6 digit codes (so they were 7 digit but it does not work and its not a good solution anyway. Hoping someone has a simple solution for me

6

Hi @Ducati,

Fully understand, you’ll find a link on how to mask sensitive data in Post #3.

A Merge (without Fuzzy options) performs an exact match lookup and can only return a value when it is present in the lookup table - so if there are no 6 or 5 digit PC in that lookup table, the result will be null.

If you know the pattern of missing digits in the PC you could add those variations to your lookup table. But there can also be an issue with the text value itself like additional spaces, unprintable chars and so on, you’ll have to examine that as well.

This is the best I can do for you without any data.
Good luck.

7

Hi Melissa
I have cleaned the original table and in the query using TRIM and removed all the unprintable characters (did not find any)

I do know the pattern of missing digits but how do I add this in the query as I would need to do it within the query so that every time.

I will figure out way to send you the problem without the sensitive data

1 Like
8

Hi Melissa
Please see attached three files related to the query above d_OCC_Location_By_Post Codes.xlsx (706.7 KB) Post Code Merge Sample Data.xlsx (23.8 KB) Query Post Code merge.xlsx (28.9 KB)

9

You will see in the Post Code workbook query that the 7 digit works fine but it returns an blank for 6 or 5 digit post codes

Hope you can help

10

@Ducati - you don’t have any 6 or 5 digit post codes in your OCC_Regions_PostCode table

I added the below column to that table, and everything returned 7 for the length

= Table.AddColumn(#“Cleaned Text”, “Postcode Length”, each Text.Length([Postcode]))

11

Hi Heather
I am sorry I don’t understand for example OX42JY is a 6 digit post code and there are many more like it

12

that number does not exist in the OCC_Regions_Postcode table, not even as the beginning of another postcode. Note that in the image below, I am filtering that table where the text begins with OX42:

image
image727Ă—170 5.76 KB

13

Hi @Ducati, did the response provided by @Heather help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

14

Problem solved there was a space in the 6 digit post codes but not the 7 digit so I removed the spaces and now the query runs perfectly