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
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 ... 🙂
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.
Yep, good call Ryan. I actually used > 1 in my final solution file that is linked in the comments. The 1 option might be a good solution for flagging duplicate OR missing dimension codes...🤔
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
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.
Sounds like something that requires some further explanation and example / screenshots. I recommend posting to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589 or community.powerbi.com/t5/Power-Query/bd-p/power-bi-services
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.
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.
@@AccessAnalytic What I meant to say was: this challenge was not the most difficult one to date. BTW: this challenge did show (again) the versatility of Power Query: Power Pivot could not handle this, needing a unique primary key in the dimension table. Also, merging tables based on multiple columns is something PP can’t do. In that sense PQ is the most robust. Yet another reason why we like PQ so much. :-)
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
You're welcome Andrew. Thanks for taking the time to leave a kind comment
Thank u SenSei. I spent a whole morning trying to figure out how to fix it!!
You're welcome :)
i am a beginner with power query and this is very helpful. thankyou for sharing your solution
Glad it helps. Thanks for taking the time to leave a kind comment.
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 ... 🙂
Cheers Steve
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.
Yep, good call Ryan. I actually used > 1 in my final solution file that is linked in the comments. The 1 option might be a good solution for flagging duplicate OR missing dimension codes...🤔
This one is absolutely a brilliant piece and hatdcore nerdy, but very useful!!!!🔥🔥🔥
You’re welcome
Thank you, always something new to learn from you!
Cheers. I appreciate you taking the time to let me know you found it useful
Fabulous. Thanks WYN👍
Cheers Kebin
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
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.
Sounds more like something suitable to Fabric and a warehouse
Very elegant solution. Would you buffers for duplicate check to speed up the query?
I don't think so, although .buffer is a dark art. I don't think it fits here
@@AccessAnalyticI used Buffer before and I remember it's a list in memory for faster processing.
It will prevent multiple calls to the source. If I don’t think that applies here
@@AccessAnalytic Thank you for clarifying my query.
Spins in my head, really😂😂😂 but I think it's great!
Cheers Orlando
What was the purpose of calculating the Max? I don't think you made use of this later.
Hi , at 13:40 in the final step I use MaxCount in the IF
Great video! But what if you wanna have that "alert" written on certain rows but still showing the rest of results?
Thanks. When adding the custom row count column you could wrap it in an IF > 1 then Alert else null
@@AccessAnalytic You mean at the very end?
Around the 6:07 mark, wrap the row count formula in an if statement
How to marge tow tables, base on customers latest visit date.
Sounds like something that requires some further explanation and example / screenshots.
I recommend posting to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589
or
community.powerbi.com/t5/Power-Query/bd-p/power-bi-services
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.
Maybe one A has a space before /after it or something different about it. Uppercase lower case can impact too
Wow clever!
Easy when you know how ! 😆
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.
Nice Geert
It's amazing how many different approaches people took in their submissions. Glad mine was logical.
@@AccessAnalytic What I meant to say was: this challenge was not the most difficult one to date.
BTW: this challenge did show (again) the versatility of Power Query: Power Pivot could not handle this, needing a unique primary key in the dimension table. Also, merging tables based on multiple columns is something PP can’t do.
In that sense PQ is the most robust. Yet another reason why we like PQ so much. :-)
Indeed!
Wouahhhhh
😆