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.
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.
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
