Forum Discussion

petrawiggin's avatar
petrawiggin
Icon for Helper I rankHelper I
2 years ago
Solved

Call out which cells are different between 2 tables (merge with multiple column keys)

Hello, experienced Power Query people! I have a huge set of data in which there are 3 columns of data that should be standardized (and then a bunch of other columns). I also have a table that has th...
  • Keezz's avatar
    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"