Removing & Keeping Duplicates in Lookup Tables | Awesome Power Query Trick 🔥

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/removing-keeping...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - [e-mail]
===== CHAPTERS =====
0:00 Intro
0:55 Capturing Duplicates
3:31 Using Records to Keep and Remove Duplicates
6:46 My Courses
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

[@damionc]

Ok take my money, I need to sign up for your course. There is not one video I don't learn something new and different especially with Power Query. You are the man.

[@GoodlyChandeep]

Thanks Damion! If you'd want to learn M language in Power Query just hand tight.. I'll be launching a course on that soon. Feel free to join the DAX and Modeling Course along with Power Query (User Interface).

[@sujit3375]

@@GoodlyChandeep m query my god Dead required And one more things Share some insights on how can we learn msbi ssis ssrs ssas too please guide us as its required

[@sujit3375]

@@GoodlyChandeep i wished i had every teacher like you I would have been far better what i m today

[@Softwaretrain]

Appreciate the explanation. I assumed you'd describe the procedure to locate duplicates and then mention a handy option for retaining duplicates with a single click.

[@paulwyatt8523]

Thank you Chandeep. The point at which you created the two tables from one was jaw dropping. I am learning so much from your videos. This one video has opened my eyes as to what is possible within 'M' and I look forward to learning from the 'Magic...records' and 'Magic...lists' videos too. I already can see uses to increase performance and save on complications created through adding columns and measures.

[@IvanCortinas_ES]

Terribly good procedure, as always a pleasure. Thank you Chandeep!

[@odel_leal]

Thank you a lot for this great trick. Keep it up. All the best! Following from Costa Rica.

[@paravej]

Thanks a lot Chandeep, explains very nicely.... Easy to understand bro!!!

[@pratheebanc6439]

Any premium course for power bi M code? I would like to learn M query and DAX and Data Modelling

[@raitup00]

One of the best channels about M Code / Power Query! Greetings from Mexico

[@GoodlyChandeep]

Glad you think so!

[@gxordi]

Marvelous! The technic with records is really magic

[@GoodlyChandeep]

Glad you liked it!

[@franciscom.paredesarias2356]

As usual, you always surprise us with a solution or tips for those of us who work with Power Query. Thank you very much for your time. Greetings from Chile.

[@GoodlyChandeep]

My pleasure Francisco !

[@Oscarromaci]

Awesome, very helpful

[@mathiassaint]

Thanks for a yet another great video! I’m having a case with similar duplicates in a dim_prod table, BUT the duplicated product row contains data that in a column that is valuable. I.e; the column prod_origin has one row with the value “France” and on row with the value “Spain”. Is there a way to consolidate the value of both rows into one row and still keep the values of all columns? An outcome could I.e be that the column prod_origin contains values that are comma separated. One the mentioned case that would be “France, Spain”

[@3Starsgamer34]

There is no doubt you are awsome in M code. An expert. But from an expert it is expected to make thing simpler rather than to make it complex just to show the probelm a big problem. 1. I want to know the reason why you choose over "All Rows" while creating grouping. we can simply count rows here itself. count of 1 means unique. isnt it. 2. At end you come to same step of counting rows taking a long route, even than one can use simple filter to create two queries but no, we want the complex way

[@williamrhopkins]

I agree. I would group by product code to get the count as you suggest and then join the unique table so the resulting table has the duplicate count. This can then be reported on in the resulting model. This is one time where I thought Goodly was needlessly complex although there may be other uses for the records technique to pass multiple tables.

[@karimallahwala7022]

Great Tricks. It is very helpful.Thank very much

[@GoodlyChandeep]

Happy to help!

[@raimundojs9547]

Excellent! Looking forward to your next video and to your course on M language.

[@GoodlyChandeep]

Awesome, thank you!

[@williamarthur4801]

I do like your use of records.

[@shafa7668]

Nice one mate as usual. Very useful.

[@GoodlyChandeep]

Glad you enjoyed it

[@-seesi-]

Can you explain why to do this procedure? You could take the Source as a 2nd query and do the steps needed. So the advantage of this approach is that you have less query steps and therefore with large datasets the queries are faster? Thanks in Advance!

[@z.719]

Awesome!

[@SACHINDAGDE]

I am trying to merge the query but the base table automatically increases the number of records from in the original table .... What is the resolution for that???

[@thebhaskarjoshi]

Excellent!

[@GoodlyChandeep]

Glad you liked it!

[@SergiyVakshul]

Are there any real benefits in using a record which is later referenced by two tables? Is loading faster that way? Have you checked? You could have created just two different queries. My guess is there wouldn’t be any difference in refresh time. Am I wrong?

[@GoodlyChandeep]

A single query that can be branched into two will perform better than duplicating queries.

[@SergiyVakshul]

@@GoodlyChandeep One of the main factors that influences the refresh duration is the number of queries sent to a data source. Having two queries does not mean that a data source will be hit twice. There is such a thing as 'persistent cache' that may reduce the number of queries sent to a data source. 'Parallel loading of tables' is another factor. I created a new file with two queries (Unique and Duplicates) only, each querying the data source with the same query. I chose OData service as a data source because using Fiddler its easy to see the actual queries sent to it. I disabled parallel loading of tables. Fiddler shows that Power Query sent only one query, the fact that could be explained by 'persistent cache' utilization. Comparing the results of using two different queries and the queries you showed in the video(the only difference is using OData service as a data source instead of Excel file) shows no difference in refresh time. The rules that Power Query follows regarding whether to send a new query to a data source or use a 'persistent cache' instead are not transparent. Criss Webb provided an explanation on the subject given by Ehren, a Power Query dev team member, in his article 'Referenced Queries And Caching In Power BI And Power Query'.

[@odel_leal]

Also the purpose methodology using records and tables in functions or apply to different scenarios.

[@danishnawaz7504]

Awsm Sir

[@GoodlyChandeep]

Thanks!

[@zaryaa7189]

Thank you❤

[@GoodlyChandeep]

Welcome!

[@rajkumarrajan8059]

Hi Chandeep, One query i have data from a cube, i am creating a Bar chart in that the Bars are sorted automatically from Largest to smallest but i want to sort it based on Year wise. But my Year column is greyed out. How can we do that?

[@rajkumarrajan8059]

This is a real life example 2020 FY - 5000 2019 FY - 4000 2021 FY - 3500 2023 FY - 3000 2022 FY - 2500 I want to sort it our based on Years

[@GoodlyChandeep]

the default chart sorting option should work. you'll see that on the top right corner of the chart

[@thamilanban]

Thank you

[@GoodlyChandeep]

Welcome!

[@krishikasentertainment7697]

U know everything. Can I know in which company do u work ? Just curious 😅

[@GoodlyChandeep]

Goodly :)

[@AbhayGadiya]

There is option in Power Query to "Keep Duplicates". Wondering it might be better than this workaround.

[@arunkumarkesavan6077]

Come on dude!!! 🤞

[@GoodlyChandeep]

Thanks!

[@vdn5716]

🆒

[@GoodlyChandeep]

Thanks!

[@gamehub8068]

May you make video in Hindi 🥺🥺🥺

[@darshanchaudhari9836]

Simple duplicates rows option is needed to do that....very confusing video 😂.. you did same that remove duplicate function does

[@omprakashthakur4515]

Hello sir 123,413,413,1352,244,12 All value in a cell How to sun excel version 2010 using function without Text to column and VBA

[@QuantumIdeas]

Awesome!!!

[@mnowako]

Excellent!

[@GoodlyChandeep]

Glad you liked it!