Power Query MERGE Challenge Solution - DUPLICATES Alert

⏬Expand to see solution files⏬
Merging tables in Power Query can be risky, so here's a way to add a warning to your output table

00:00 Intro
00:20 Recap
02:30 Solution

See my solution file and everyone's submissions

Connect with me

Did you know I've written a book "Power BI for the Excel Analyst"?

[andrewsinha]

Hi Wyn, This is very useful to me as I work with many ad-hoc spreadsheets that cannot always be checked for duplicates before loading into PQ. Thanks again. Andrew

[Soulenergy]

Thank u SenSei. I spent a whole morning trying to figure out how to fix it!!

[shamafirdos]

i am a beginner with power query and this is very helpful. thankyou for sharing your solution

[Mister_Bates]

Loved your use of Table.FromList (with the optional columns parameter) - I've been using #table() up until now, but the downside of that is having to provide values for each column ... 🙂

[ryan_haigh]

This is a nice solution, will be interesting to try with larger datasets and multiple joins and whether it can use native query. One small thing, filtering <> 0 will falsely report rows with no matching value in the joined table (possible with left outer join) as duplicates, better to use > 1.

[mandypaulissen]

This one is absolutely a brilliant piece and hatdcore nerdy, but very useful!!!!🔥🔥🔥

[alexbarbucristi]

Thank you, always something new to learn from you!

[shreedharan.moorthy]

This is great. However not able to overcome duplicate, since my database is huge, and there is no unique identifier available.
I merged using three different columns, which is po number, Sty code and colour codes.
Each one having multiple rows based on colour code and quantity

[shwetkumar]

Hello Sir,
I have a historical table and Another table is Delta table which have rows that comes daily.
now it wants to upsert that delta table match each row with historical table and when it matches then update it with delta rows and if match not found then insert the rows.

[Bhavik_Khatri]

Very elegant solution. Would you buffers for duplicate check to speed up the query?

[ohdjrp]

Spins in my head, really😂😂😂 but I think it's great!

[brianxyz]

What was the purpose of calculating the Max? I don't think you made use of this later.

[rameshnaidu]

How to marge tow tables, base on customers latest visit date.

[mirrrvelll]

Great video! But what if you wanna have that "alert" written on certain rows but still showing the rest of results?

[vijaykrishnan]

I see the merge showing me the first instance always just like vlookup in excel So i am not sure if this issue is fixed or am i looking in the wrong place? For eg if i have A as Apple and another entry with A as avacado, merge is always showing A as apple and its not doubling up after merge.

[GeertDelmulle]

Nice solution, albeit a bit predictable.
And yet, it’s the unexpected that’s the most fun: those extra arguments in Table.FromList.
BTW, here’s how I make intellisense work: before I start typing the name of a function, I type an opening parenthesis (“(“), then arrow left and start typing the function. Then it works perfectly. Give it a try. :-)
Cheers.