How To Remove Duplicate Rows In Power Query Based On Conditions
ฝัง
- เผยแพร่เมื่อ 15 ก.ย. 2024
- In this video, you will see how to clean up transactional data in Power Query, including a hack to generate M code for multiple nested table transformations by just using the User Interface and a method for creating a Running Total in Power Query.
****Video Details****
0:34 three scenarios formulated
1:44 adding column with absolute quantities
2:10 group by ID index and absolute quantity
2:30 add a new column - sum of the quantity
2:47 add aggregation - AllRows
3:27 add custom column for nested table transformations
4:22 build transformation - add new query
4:38 sort on movement type
4:57 sort posting date
5:15 List.FirstN function
6:15 add index column
6:34 custom column - running total
8:24 add filter condition
8:32 remove helper columns
8:47 advanced editor
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterpri...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterpri...
FREE - Power BI Resources - enterprisedna.c...
FREE - 60 Page DAX Reference Guide Download - enterprisedna....
Enterprise DNA Membership - enterprisedna....
Enterprise DNA Online - portal.enterpri...
Enterprise DNA Events - enterprisedna....
This is an amazing use of transformation within nested tables. The use of "insert as new query" and then copying the M is so simple yet powerful (I raise my hand and admit I never thought of that!!). Thank you very much, I thought I was all that and a bag of chips by doing transformations within the nested table (i would do "fake" steps and copy the steps into the Notebook, alter then ....)
Hi Raul Jimenez, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Amazing Job and teaching, thank you for that! Just a reminder: If you're doing this on your on way, always check the last step before do your custon column "Running Total", because it may led you to this error: "Expression.Error: A cyclic reference was encountered during evaluation". Using de example of the video, minute 7:25, custom column is "List.FirstN ( #"Added Index"[Qty], [Index]). After that, if you add any steps between Added Index and Custom Column, you HAVE to change the code of the custom column. It may seem clear right now as i'm explaining, but often times we make this mistakes.
Hi Leandro, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Well done Melissa, great techniques showcased here
More power query videos please! I know Sam does a lot of DAX and we love those videos but PQ is a great supplemental workflow for the pre-modeling stage where you don’t want to use TSQL.
Hi Joshua Harvey, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
You're a magician Melissa!
Amazing!!! Thanks very much I`ve been looking for same solution...
Hi Achal Barla, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Excellent. Need to watch it again Feel sure I will need to use this routine. Great. Thanks so much,
Hi Erica Dyson, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Amazing stuff Melissa 👏
thanks for sharing your great intuitive knowledge 👍
Amazing! Thanks Melissa!
Interesting approach. You could have also set that up as a function based on a query parameter to have invoked against the original table’s custom column [AllRows] nested tables especially if you needed to use that across multiple tables where you didn’t want to union them first
Hi Joshua Harvey, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
I agree with the custom function, like you said. It came to mind as soon as i saw that nested let. I would think it easier to manage and see what is happening in the transformation process especially after time has passed and you need to make a modification
Very informative and clever.
Hi Anthony, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
םOh but I am already.. and you've been a big help to me and continue to be so!
Hi Erica Dyson, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
This is really good technique.
Hi Sai kiran Kusumanchi, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials.
Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Very good Melissa
Great tutorial thank you!
Hi Συμεών Παπαδόπουλος, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Genius
Hi Jhorman Gomez, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials.
Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
I am trying to remove duplicate base on serial numbers. My problem is that the field that are our agents filled out is free txt and sometimes put special characters for Non-Serial products.
how can I treat special characters as unique versus products that have 15 digit serial numbers. special characters are not limited to (No value, *, -, ).? thanks.
Hi arman,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful! You can subscribe to our TH-cam channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
I have a Query that has a daily version of the operation’s summary. This Query has a column with the date of the file’s creation and I want to keep the lastest version of the last date of each month. Could you save my life?
Hello Pablo Moreno,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful! You can subscribe to our TH-cam channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
amazing!!!
Hi Pedro Cabral da Camara, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials.
Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Thanks 👍
Hi Manish Sawant, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Awesome Melissa! Thanks.
Hi Craig Tysall, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html