Merge queries not returning matches

[jivens]

Hi all,

I'm merging two tables in Power Query and it's not returning matches when I know there should be (my dataset is small at this stage and I can basically see the matches with the naked eye 🙂

The columns I'm selecting to match are in the same format (text), with the same case and no trailing spaces.

The strings of text in these columns are a bit long (~60 characters) and do not have the same length across rows - could that be the reason? Anything else I might be missing?

Thank you!!

Jiv

[jivens]

Thank you SOOOO much, that was indeed the issue. It's fixed and I'm doing a happy dance!!

[v-xinruzhu-msft]

Hi @jivens 

1.You can remove the space of the DD_REL column in two tables

Table A

Table B

2.Then combine the two table 

Output

You can refer to the attachment.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jivens]

Thank you for your time!!

None of the tips did the trick... here's what my data looks like. I'm trying to merge using the column "DD_REL".

Table A

subjectname	DD_REL
00599-002	00599-002VALUE A -  #1
00503-007	00503-007VALUE C -  #1

Table B

subjectname	recordid	DDYN	DD_REL
00599-002	1	Yes	00599-002VALUE A - #1
00847-003	1	No	00847-003VALUE A - #1
00503-009	1	No	00503-009VALUE A - #1
00503-015	1	Yes	00503-015VALUE A - #1
00847-002	1	No	00847-002VALUE A - #1
00503-008	1	No	00503-008VALUE A - #1
00847-001	1	No	00847-001VALUE A - #1
00503-010	1	No	00503-010VALUE A - #1
00847-004	1	No	00847-004VALUE A - #1
00503-007	1	No	00503-007VALUE A - #1
00599-004	1	No	00599-004VALUE A - #1
00599-005	1	No	00599-005VALUE A - #1
00503-011	1	No	00503-011VALUE A - #1
00599-006	1	No	00599-006VALUE A - #1
00503-014	1	No	00503-014VALUE A - #1
00847-005	1	No	00847-005VALUE A - #1
00599-001	1	No	00599-001VALUE A - #1
00847-002	1	No	00847-002VALUE B - #1
00847-004	1	No	00847-004VALUE B - #1
00503-010	1	No	00503-010VALUE B - #1
00599-002	1	No	00599-002VALUE B - #1
00503-009	1	No	00503-009VALUE B - #1
00503-011	1	No	00503-011VALUE B - #1
00599-004	1	No	00599-004VALUE B - #1
00847-001	1	No	00847-001VALUE B - #1
00599-005	1	No	00599-005VALUE B - #1
00599-006	1	No	00599-006VALUE B - #1
00847-003	1	No	00847-003VALUE B - #1
00503-007	1	No	00503-007VALUE B - #1
00503-008	1	No	00503-008VALUE B - #1
00599-001	1	No	00599-001VALUE B - #1
00847-005	1	No	00847-005VALUE B - #1
00599-001	1	No	00599-001VALUE C - #1
00847-005	1	No	00847-005VALUE C - #1
00847-003	1	No	00847-003VALUE C - #1
00503-008	1	No	00503-008VALUE C - #1
00847-004	1	No	00847-004VALUE C - #1
00599-005	1	No	00599-005VALUE C - #1
00847-001	1	No	00847-001VALUE C - #1
00847-002	1	No	00847-002VALUE C - #1
00503-007	2	No	00503-007VALUE C - #2
00599-006	1	No	00599-006VALUE C - #1
00503-007	1	Yes	00503-007VALUE C - #1
00503-014	1		00503-014VALUE C - #1
00599-004	1	No	00599-004VALUE C - #1
00599-002	1	No	00599-002VALUE C - #1
00599-004	1	No	00599-004VALUE D - #1
00599-006	1	No	00599-006VALUE D - #1
00503-011	1	No	00503-011VALUE D - #1
00503-008	1	No	00503-008VALUE D - #1
00503-014	1		00503-014VALUE D - #1
00847-001	1	No	00847-001VALUE D - #1
00847-002	1	No	00847-002VALUE D - #1
00847-003	1	No	00847-003VALUE D - #1
00503-007	1	No	00503-007VALUE D - #1
00599-005	1	No	00599-005VALUE D - #1
00503-015	1		00503-015VALUE D - #1
00599-001	1	No	00599-001VALUE D - #1
00847-005	1	No	00847-005VALUE D - #1
00599-002	1	No	00599-002VALUE D - #1
00847-004	1	No	00847-004VALUE D - #1

[BA_Pete · Accepted solution]

Hi Jiv,

It looks like your TableA [DD_REL] column has a double-space before each of the '#1' parts.

Try right-clicking on the TableA[DD_REL] column header and select Replace Values. Then replace "  " (double-space) with " " (single space), then do the merge after this step. That worked for me:

Table.ReplaceValue(PreviousStepName, "  ", " ", Replacer.ReplaceText, {"DD_REL"})

Pete

[BA_Pete]

Hi Jiv,

Power Query supports text strings of up to 32,766 (unicode) characters, so I don't think that's the issue.

In my experience, it's usually one of the following fixes, in rough order of likelihood:

-1- Refresh the result preview by going to the Home tab > Refresh Preview

-2- Trim the matching columns in both tables (Transform tab > Format > Trim)

-3- Upper- or lower-case the matching columns in both tables (Transform tab > Format > UPPERCASE or lowercase)

-4- Clean the matching columns in both tables (Transform tab > Format > Clean)

I've not listed matching the Data Types as the Merge dialog should give you a warning for this.

If none of these work, then you'll need to provide a small amount of anonymised sample data from each matching table to see if the issue can be reproduced and fixed accordingly.

Pete