Powery Query - Is it possiblee to merge with duplicates?

[gimmeexcel]

[h=2][/h]

Hello everybody,

I'm just trying to get clarity on this because it seems that the only way to merge using Power Query is with unique values.

So basically I have 2 tables. My main table has a column with cells that contain a few duplicates. The column in the lookup table doesn't have any duplicates. Is there a way to return the same value to each duplicate in in the main table?

Any input would be greatly appreciated.

Thanks!
​

 

[Matt Allington]

Are you doing a table merge? Yes this works fine. Load your table with duplicates as the fist table, the other as the second table. Select left outer join, then expand the new column of data

 

[gimmeexcel]

Thanks for the feedback, Matt. Unfortunately Power query is not matching any of the data.

 

[Matt Allington]

can you post a sample of the workbook? It does work, so something is clearly wrong.

 

[gimmeexcel]

Hi Matt,

Thanks again. I think this a rather unusual situation and there may not be a real solution.

I didn't see a way to upload a workbook, so I'm providing a link to a Google Sheet instead:
https://docs.google.com/spreadsheets/d/1BeyZ4kmYNt8iTVwfBVfMUB7JQL-e5FvCea6UViJyTOI/edit?usp=sharing

 

[Matt Allington]

What version of Excel do you have? This is done in 2016 https://www.dropbox.com/s/bfw3wl5ucx1x3tj/demo.xlsx?dl=0

I tried to video it (3 times) but it kept failing to record, so hopefully you can open the file

 

[Matt Allington]

OK, did it on a different PC. Here is the video https://youtu.be/PJVrG1-LapI

 

[gimmeexcel]

Thanks so much, Matt. Really nice of you to help and provide a video.

When I try to repeat what you did, I get the same result as you. However when I try it with other data, it doesn't work.

It has to be something wrong with my data set (below). I don't want to take up a lot of your time. But I'm hoping you could just take a quick lot at my tables and tell me what's wrong with my data and why it won't merge.

https://docs.google.com/spreadsheets/d/1bh_zoiDWeIaCtOFXfAesi-vOJocHNzjpuFmtw--eB8I/edit?usp=sharing

 

[Matt Allington]

A what actually happens? Do you get any joins at all? I haven't tested it, but the first thing I suggest is to trim and clean both columns before joining. This will remove leading and trailing spaces (that you can't see) and also non printing characters. The values must be identical for a join to succeed.

Select one of the columns, then go to Transform\format and apply the trim step, then the clean step. Repeat for the other column in the other query. Then try the join again. I suspect that will fix it. If not, try manually cut and paste the value from one cell in one of the tables to (what seems like) an identical row in the other table. This way you know for sure that these 2 rows ARE identical. Try the join again and see if this one row at least works. This will confirm the problem.

 

[gimmeexcel]

Thanks for following up Matt. And thanks for the great tip.

Out over 1,100 only 30 or so are not matching now. I need to look through to see if the unmatched are just missing in one of the tables.