Log In
Register
Home
Active Member
November 27, 2020
Offline
Goodday all from the Netherlands,
I'm a newbie in Power Query and M but getting a better understanding every time I read posts in this forum and website (thank you for that)
Would you be so kind and have a look at my tables in attached sheet.
For testing purposes I've created dummy data and all tables in 1 sheet.
Goal is to use Power Query to create:
4 tables as the result of comparisons of 2 tables
1 Old table shows ID 1003, but New table does not > 1003 should show in table Removed
2 New table shows ID 1006, but Old table does not > 1006 should show in table Added
3 If an ID shows in both table (Old & New) > ID should show in table Identical
4 For each ID in table Identical check if all the values in that row are the same as the values for that ID in Old Table
I've managed tables 1 to 3 and somewhat created table 4 but this last table is not completely as I would like it to be.
Can you help me to create table 4 (Adjusted) with Power Query and:
- avoid hardcoded columnnames as production tables contain approx 80 columns (to many to handle properly)
a bonus would be if the actually changed values get highlighted for the user.
(otherwise use Conditional Formatting or VBA?)
Please have a look at my file and teach me how I should handle this.
Thank you for your time.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Heleen,
What you should have in mind is that each row in a table is a Record.
If you add a new column, you can combine the current Record (row).
Other things you need to know:
each is the same with: (x)=> (where x is the record, the entire row data)
To combine a record, we convert to List: Record.ToList(x).
To combine all List items, we pass the list to Text.Combine, but before combining, we need to convert to text using List.Transform:
(x)=>Text.Combine(List.Transform(Record.ToList(x),each Text.From(_))," - ")
I adjusted a few queries for you, should be flexible now.
Active Member
November 27, 2020
Offline
Hi Catalin,
Thank you so much for your help.
It took me some time to figure out what you meant with the X being the entire row data. With your adjusted queries in the file I managed to figure it out.
Without your help I would never have thought of Record.ToList.
Grateful for you guidance.
Thanks again.
Regards,
Heleen
1 Guest(s)
