Easily Compare Multiple Tables in Power Query
Vložit
- čas přidán 4. 08. 2021
- Learn how to easily compare 3 or more tables in Power Query and avoid having to do lots of complicated query merges.
0:33 Power Query Lists (Table Columns)
1:02 Checking Source Data
1:30 Load Table Into Power Query (Connection Only)
2:30 Using #shared to Create Function Reference Table
3:20 List.Intersect
4:28 Filtering Table Using Table.SelectRows and List.ContainsAny
6:33 List.Union
8:13 List.Difference
Download the Excel file that includes the data and queries in this video: www.myonlinetr...
View my comprehensive courses: www.myonlinetr...
Connect with me on LinkedIn: / myndatreacy - Věda a technologie
Amazing! Super efficient way of saving minutes, hours and quite possibly days of manual and boring work. Thanks again Mynda and Phil!
Our pleasure Susith.
I agree! Thank you for sharing!
Thanks Phil for such a clear demonstration of these LIST functions. I hope you do loads more MCode videos!
Yes Grainne, more to come hopefully.
What a level of expertise, this is a way of professional power query gr8 video.
I will learn and practice to implement in my daily work efficiently.
Please continue power query videos.
Thanks a lot
Thank you Shoaib. I certainly intend to keep doing PQ videos like this. Regards, Phil
Explaining with real time use cases and hidden gem functions is awesome.
Thx Naveen.
Thanks Mynda and Phil. I will be watching this one again when I'm at my desk...
Glad you enjoyed it Matt.
That was simply glorious!! I really enjoyed the explanation. Very calm and well spoken, both of you!! Please make more videos about tables in power query. Thank you!!
Thank you! Will do!
It was very nice learning something new. I would have appended all three trainings. Transformed text to Proper. Grouped by Name for a count rows. Then add a merge query step to the Staff query to pull in the count. Add Conditional column that says if the [expanded column] equals null then 0 if not then [expanded column]. I think it’s a rather simple way to do it but just depends how you want your data presented.
Thanks for sharing 😊
Excellent tutorial Mynda. I love your dedication and how well you explain it. Thank you for telling us these List functions of M language.
Glad it was helpful Iván.
Saving Time and Saving Minds.....KEEP THEM COMING PLEASE!
Will do.
Hey Mynda truly appreciate for all the videos you create & share - be it excel or power query...they are full of information, knowledge and are of great help, as always.
List.Intersect and union is great way to do away with merge - which takes time when you have large dataset...was looking for this so long...thanks
Thank you for this helpful tutorial, knowing the functions and how to use them make us understand the essence of PowerQuerry and be more confortable using them
You're very welcome!
Another approach - i) simply append all four tables - ii) do groupby & iii) subtract 1 & sort. Four lines of code gets you a nicely sorted table:
Source = Table.Combine({Staff, Training_1, Training_2, Training_3}),
GroupRows = Table.Group(Source, {"Name", "ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
Subtract = Table.TransformColumns(GroupRows, {{"Count", each _ - 1, type number}}),
SortRows = Table.Sort(Subtract,{{"Count", Order.Descending}})
Thanks for sharing, Drew!
FANTASTIC INFORMATION, thank you for sharing this, I have so many uses for the list function now that I have seen the examples you have shown.
Awesome to hear!
Very informative and useful.. Specially the speaking style of the lecturer is simply wonderful.
Thanks for your kind words 😊
Thank you Mynda & Phil for this super useful video! I just have a need for a similar data problem I am handling and this video is just in time to help me! Really appreciate! 😊👍
Glad it was helpful Vijay.
This is fabulous! Set theory in action!
I’ve always dreaded merges given how slow they can get. Going to have to dig more into list functions. Thx so much for sharing!
Glad you enjoyed it Benson :)
Big Thx to Mynda and team, yet another informative video.
Glad you enjoyed it Ripudaman.
Thanks Mynda and Phil. List functions are so powerful. Love to learn more ways to use them. Thanks for sharing :)) Thumbs up!!
Glad it was helpful Wayne.
Another great video! Thanks Mynda & Phil.
No worries David.
Very Powerful and simple technique. I like it!
Thx Chris, glad you liked it.
Glad you like it!
Very good video. I'd have appended the three tables, using the training day (1,2,3) as an ID column then used power pivot for the rest, however this is a nice alternative. I'm always wary of producing my final result in power query (preferring to use it solely for transformation), but I'm not sure if my instincts are correct in that regard.
Thanks Rico. Yes there are certainly other ways to achieve a solution. As I said in an earlier comment, I just want people to realise that table columns are lists and as such you can use List Functions to manipulate the data.
@@MyOnlineTrainingHub I didn't even catch that part in the video (adult ADHD has its pros and cons), but that helps demystify Lists for me. Glad I read this comment.
Love it! ❤ Really useful technique.
Happy to read that, thank you 😊
EXCELLENT!!! Thank you for taking the time to create this video
My pleasure 😊
It's really that much easy.. Thanks for the tutorial..! Great work..! 👍👏
Thank you SM
Hi Mynda! Some Really Helpful Tips And Techniques From Phil...Thank You Both :)
Glad it was helpful Darryl.
Another brilliant example on combining things in PQ. Amazing the way you think and approach PQ.
Thanks a lot!
I tried to use this approach with a table having 160.000 rows to select only ones matching my intersect list of 786 items, in place of my old method merge query and then removing the blanks, but it is super slow. Maybe in my case the old method remains faster for computation.
Thanks, Alberto!
Wow, opens my mind.
Great to hear 🙏
Excellent! Very useful
Glad you think so!
Mind blowing! Thanks for sharing.
Glad you liked it Jeremy.
Request to share more videos on such list examples and M Codes . Advise how can we learn M Code fast
Thanks for the suggestion, Syed 😊
When I was young my dad gifted me an axe. I was super stoked about using it but there was no wood. I had to wait until we got some wood months later for winter to use that axe. I often feel like that boy with the axe when I see these cool tricks yet have no application needs. :)
Nice analogy, Dayve! Tuck them away...one day they'll be useful :-)
No words. Just thumbs up!
Thanks Pavol
Just tried it on my tables. Very nice. I typically merge tables according to a unique combination of 2+ columns (ex. "Location", "Account", same accounts in various locations). Is it possible tu use this method somehow?
Amazing !! useful in power BI, multiple columns and multiple tables
Glad you liked it 😊
Thank you so much for such a knowledgeable video, its just awesome
Glad you liked it
Thank you for the helpful tips.
No problem Brian.
Great Tutorial Linda & Phil..!! Keep up the good work..!!
Thx Sri, we will.
Thanks, will do!
I am speechless. So simple and so powerful. How to learn how to understand the way those functions work to be able to use similar techniques on my own? CZcams, training, literature?
Glad it was helpful!
@@MyOnlineTrainingHub How to develop the skills to the level you have showed in the clip? I mean operating M language in that way? I am fascinated, obsessed, filled with desire to be able to get closer to that level in a more organised way. This is why I have asked the question about how to make it happen? Do you have M language focused training?
The trick at 05:55 is amazing 👍
Glad you liked it :-)
@@MyOnlineTrainingHub I always learn something when I watch your videos 😉
Super. What is faster, List or Merge ? I have thousands of rows and 20 columns.
Thanks for this, looks very powerful -
Thx Clive It really is.
Thank you. Good demonstration
Glad it was helpful Martin.
Top notch.
Thanks so much!
thanks. perfect
Glad it was helpful.
thanks a lot.
You're welcome Edu.
Very well done!
Thanks Adam.
I just spend 2 hours with join steps. For some reason the "ANTI" joins are backwards to me.. but still work. This list technique will replace all the merges for me
Thanks!
Glad it was helpful!
A GREAT TOOL, THANKS LOT
You're welcome!
Informative video. Saved video in a list of favorites =)
Thx Mirrr, glad it was useful.
Thank you Mynda, thank you Phil. Always thankful for your tutorial.
Quick question, on the Function reference query, as we click on the value column (contains function), how do we switch between showing function description in the window below versus adding a step in creating value in the query?
Thank you and if you might taught us already that I've overlooked, my apology.
Thanks Summer.
You mean after I show how to use #shared? That's a query just for looking at the functions so to add a step to the main queries, click back on one of the main query names then add a step as you would normally, either via menus or right clicking beneath the Applied Steps and adding one.
Phil
Hello, nice function demonstration, but the most impressive for me is the helper assistant that shows automatically the syntax of the function below the formula bar of PQ during the encoding of the function and parameters . How do I activate this on my Excel? Is it an add-on? It it working also when using function in the add custom column interface? Thanks for any feedback
Thanks, Pierre. The intellisense tool in Power Query is available to those with a Microsoft 365 license.
Excellent video! This is what I was after. Does anyone know how to do a True/false test on the List.Intersect function?
Glad it was helpful. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
This is awesome Phil. what if after getting the list I need to get the data fields from multiple tables which we have used in the list.intersection function. can you please help me on this.
Thanks Sadineni. I can think of a few ways to do this but it could be tricky depending on what you data looks like. Can you please start a topic on our forum and supply some sample files so I can have a look.
@@philiptreacy714 Sure Philip I can do that
Thanks for this! Is this method a much more efficient way to do a Vlookup type thing compared to Merging tables? Are there any times when it would be better to use Merging? I'm thinking maybe if I need to pull in all the columns from both tables? Would it be better to use Merge then instead of adding lots of columns individually using list functions? I have a query that I run every month that works well, but it is SO slow (as in it can sometimes take over an hour to run). My query uses quite a lot of merges, so I'm really looking forward to trying this method. Thanks!!! :D
On the "Attended No Days", can we use just the name of "Attended At least one day" to exclude, insteadt of putting formula?
Hi Alpay,
Almost! You'd also need to reference the Name column too, just using #"Attended At Least 1 Day" would return the whole table from that query.
So this would work:
= List.Difference( Staff[Name], #"Attended At Least 1 Day"[Name] )
Cheers
Phil
I love this method and want to be able to use it across all of the different reports I have to create and/or maintain. For some reason tho, when I take this approach and follow the exact steps in the video, my excel file jumps in size and and take upwards of 10 minutes just to load the data. If i remove these steps within my power query, my excel file goes back to a much faster loading state.
A couple things to note - I am working with data that I am loading externally from a OneDrive Business account. The data lives in this external data source on 4 separate tabs each formatted as an excel table. I copy and paste data from an ERP so that it can be viewed and formatted for the purposes of reporting.
My questions/need for some guidance: Would having a power query, based on external data, limit the usability of some basic query functions (like List.Sum or List.PositionOf)? if not, what are some things I should consider that could be bogging down the speed at which my data can be loaded within PQ and ultimately back into Excel for Pivot Tables and such?
Hi Michael,
Hard to say exactly without seeing your code. You say you are doing exactly as shown in the video - from what point? Just calling the List functions? Perhaps you need to use the List.Buffer function, really not sure what you are doing without seeing your code. Can you post it on our forum for me to have a look at www.myonlinetraininghub.com/excel-forum
Phil
I have 2 tables and one column have the same values in both tables but i need to check them if true or false and return only the false one. is this explained in the video ?
Great!
Thx!
Also, can you compare tables based on multiple columns? Like when you merge tables based on exact match of First name and Surname?
When will your videos come out about the new matrix functions BYCOL, BYROW, ISOMITTED, MAKEARRAY, MAP, REDUCE and SCAN? 😀🤗
Haven’t made plans for tutorials on those functions yet 😉
Thanks Phil and Mynda!
But if I just need to make the intersection list and then do a filter based on the intersection list, is it more efficient doing it this way or just using the traditional way of merging and give tag "exist in another list" sort of way? I have 200k rows of data to be checked to 50k data btw. I'm also going to check the duration of both methods actually.
Hi Lukito,
Not sure what you mean by 'do a filter based on the intersection list'. Are you filtering the list created by the intersection? Not sure how the filtering is related to using List.Intersection vs merging tables.
You may find that using List.Buffer on your lists gives improved performance.
Regards
Phil
Thanks and But if I want to get at least 2 days or n days trainings, how to get it.
Thanks for this very helpful vid. One problem though: I tried to download the example file, but the link didn't work.
I tested the link. It works for me. Be sure to read the instructions on the page. If you still have problems, try a different browser. Hope that helps.
@@MyOnlineTrainingHub Thank you for your quick reply. Now it works. I don't know where the problem was. Anyway, thanks again.👍
This is more concise but seems slower in terms of performance. Maybe you can compare the pros and cons of each method next time?
Good idea, Eric.
I tried to use this method, but the data takes a long time. I'm pulling it in from sharepoint.
Sorry, where can I find this green excel shirt.
😊 redbubble has them.
So does "Union" mean at least one of them?
A union requires you to specify at least one column to match in each table.
I'm a total noob and I got lost at 1:30
I mean, if the table is already loaded (because we see it there), I don't get the point of creating the connection. Doesn't the connection already exist, since the table Training 3 is on screen?
Hi, you see the tables on screen because they are in the Excel sheet. But that doesn't mean they are loaded into Power Query. You must load the data into PQ to do any PQ transformations on it. The transformed data can then be sent back out to the Excel sheet into a new table. Please check out our PQ course to learn more www.myonlinetraininghub.com/excel-power-query-course
I do this quite a lot, But the way I do it is I add a column to each table and fill in the Table name for each record, I them append all the tables to one master table and I then use a pivot table, I add the table name to the Columns & the Values fields. am I mad for doing it this way? It works good for me when I getting data form a number of files in a folder.
I'd do it that way too, but it depends on use case. Obviously the video contained just an illustration rather than a working example. I can't imagine any real world requirement for creating three lists. Rather you would have a dimension table of names (and maybe one of training session details) and a fact table containing the three training session attendances as you suggest. You'd then perhaps calculate the number of training sessions attended in Dax, or a percentage of sessions attended or whatever. The problem with creating lists is that the very next request from management will be another item of data that you'll need another list for! That would soon add up and become inefficient, having the data in a single fact table would require just a new measure for each request.
Again, the purpose of the video was to show the list functions, which it does very well.
Hi Liam,
As Rico said, it depends on your own case and what you want as a result. If you've got a way that works then great. I'm trying to present an alternative that people may not have realised is possible.
The key point that I'm trying to get across is that table columns are Lists and as such you should be thinking, can I use a List function to do what I need?
You won't always need or want to use List functions, but people should be aware that they can.
Regards
Phil
@@MyOnlineTrainingHub Yes, It's good to learn new ways of doing things. I never know when I come across something new and I need a different way of doing something, that's why I love watching your channel. Keep up the good work. It's much appreciated 🙂
Great video - BUT the music was both annoying and distracting
Will keep it in mind, Andrew!
This guy made me sleeping.
Thanks for the feedback 🙏
Too complicated for me
If you need a hand, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
1.2k likes, 18 dislikes. Fire the CZcams CEO for protecting sponsors and removing control from users.
For me: Power Query is too difficult
Sorry to hear that, Otto. This is an advanced tutorial. Have you seen my introduction to Power Query which doesn't use any coding: czcams.com/video/L4BuUzccLpo/video.html
At first you may struggle but if you can keep trying for a while you"ll get the hang of it.