Easily Compare Multiple Tables in Power Query - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Maki
Thanks Philip/Mynda,
Good tutorial to know list functions. I did the same exercise, but in very simpler way and much shorter following steps below:
1- Beginning, I did the same. I loaded all four tables in power query as connections only.
2- Adding custom column using Test.Lower() formula to all 4 queries. This to make all names with lower case.
3- I merged the 3 training queries to the staff query using lower case-names columns as common .
4- Now I have original Staff query with additional 4 lower case name columns.
5-Adding new column (by example) by counting how many names appeared in the last 3 columns manually. After adding 2 or 3 cells, the values in the rest of cells will appear automatically with numbers 0-3.
6- Delete 4 columns of lower case names. The remaining will be the original staff query and a column counting number of training attendings.
7- Now this query can be loaded as a table then can be filtered as the user like (using 1 table only).
8-Or if the user wants individual table, then the staff query can be copied 4 times. Each copy can be filtered to the specified number then delete the counting column and load as table to the workbook.
Regards
Maki S. Hussain
Baghdad-Iraq
reply
Thanks Philip/Mynda,
Good tutorial to know list functions. I did the same exercise, but in very simpler way and much shorter following steps below:
1- Beginning, I did the same. I loaded all four tables in power query as connections only.
2- Adding custom column using Test.Lower() formula to all 4 queries. This to make all names with lower case.
3- I merged the 3 training queries to the staff query using lower case-names columns as common .
4- Now I have original Staff query with additional 4 lower case name columns.
5-Adding new column (by example) by counting how many names appeared in the last 3 columns manually. After adding 2 or 3 cells, the values in the rest of cells will appear automatically with numbers 0-3.
6- Delete 4 columns of lower case names. The remaining will be the original staff query and a column counting number of training attendings.
7- Now this query can be loaded as a table then can be filtered as the user like (using 1 table only).
8-Or if the user wants individual table, then the staff query can be copied 4 times. Each copy can be filtered to the specified number then delete the counting column and load as table to the workbook.
Regards
Maki S. Hussain
Baghdad-Iraq
reply
Michael
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?
reply
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?
reply
Lee
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
reply
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
reply
Drew
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--)
reply
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--)
reply
Alberto
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.
reply
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.
reply
Summer
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.
reply
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.
reply
Lukito
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.
reply
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.
reply
Pierre
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
reply
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
reply
liam
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.
reply
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.
reply
Rico
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.
reply
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.
reply
Add a review, comment
Other channel videos
4:14
