Forum Discussion
Call out which cells are different between 2 tables (merge with multiple column keys)
- 2 years ago
Sure. I added comments to the code. The syntax of the M language is very concise, making it cryptic at times, but I hope this helps. There a re many good resources on Youtube on PowerQuery and the M language if you want to get a better understanding of M. M is awesomely powerful and if you have complex data cleaning jobs regularly, it's worth it!
let Source = #"Data Values", // Getting the source data from the workbook #"Added Is Type Correct" = // adding a column with a true value if the Type is correct and otherwise false Table.AddColumn(Source, "Is Type Correct", // The Table.AddColumn starts with an input table and the name of the new column. It will loop through all records and call the function you supply in the next parameter. each /* `each` is a shorthand for a function definition. it is equivalent to `(_) +>`, The function will be called by Table.AddColumn with 1 parameter: The current record. So, within the function you can refer to _ if you need to refer to the current record. */ List.Contains( // The List.Contains will do the actual checking #"Acceptable Values"[Type] /* This refers to the column [Type] in the table #"Acceptable Values" from the spreadsheet. This is a list of all the values in the colum */ , [Type] /* And this is short for _[Type] and refers to the field [Type] in the xurrent record. List.Contains will see if this value is in the list.*/ ) // ending List.Contains ), // Table.AddColumn #"Added Is Thing Correct" = Table.AddColumn(#"Added Is Type Correct", "Is Thing Correct", // adding another column each List.Contains( // again, a function `(_) =>` Table.SelectRows( /* function to get rows from a table. This function is also used when you filter using the UI we want a list of Things matching the Type in the row we are adding the column to */ #"Acceptable Values" // The table to select from: Is coming from the excelsheet heet , (acceptable) => /* SelectRows also needs a function with 1 paremeter: the current row We can't use `each` here because it would conlict with the `each` in the AddColumn I named the parameter "acceptable" */ acceptable[Type] = [Type] // select the rows from Accepatble values where the Type = the Type in the current row ) // end select rows [Thing] // From the return selection of rows, get the column Thing as a list , [Thing] // See if [Thing] in the current row exsts in the accepatble [Thing]s ) // End list contains ), #"Added Is Price Correct" = // similar to the previous colum, only atching 2 columns Table.AddColumn(#"Added Is Thing Correct", "Is Price Correct", each List.Contains(Table.SelectRows(#"Acceptable Values", (acceptable) => [Type] = acceptable[Type] and [Thing] = acceptable[Thing] )[Price] , [Price] ) ), #"Added Is Correct" = // New column returning true when all 3 new columns are true Table.AddColumn(#"Added Is Price Correct", "Is Correct", each [Is Type Correct] and [Is Thing Correct] and [Is Price Correct]), #"Filtered Only Errors" = // Only return the rows in the #"Data Values" table with errors Table.SelectRows(#"Added Is Correct", each ([Is Correct] = false)) in #"Filtered Only Errors"
If you have a mismatch there wil definitly be multiple rows in the acceptible values table that are NOT matched.
So how do you pick the right one?
If you assume (wild assumption here!) that only 1 field is wrong, you can find out which one is wrong by looking for the ones that are right. But beware: There may be multiple "acceptable values" for any combination of 2 fields filled correctly...
For example if I have additional acceptable value:
Thing Type Price
Apple Fruit $3
In that case
Kiwy Fruit $3 Mary
Will not match 2 acceptible values rows.
And if you have a data row as follows??
Kiwy Fruit $5 Mary
So a merge join will not work...
If you can elaborate on exactly what you are looking for in these situations, I may be of help...
What should be unique in the acceptable values?
I guess
- There is an exhaustive list of Types
- There should be one price for every combination of Thing+Type
In that case
- Fisrt check for valid Type
- Then check for valid Thing Type combination to see if the thing is correct
- Then check the price against the found Thing Type combination.