none
Check for similar group of data on different worksheet RRS feed

  • Question

  • Hi,

    Need help on data matching. I have a two sets of data on 2 worksheets and want to check how many group of rows are same as in the first sheet.

    Sample of two sets of given below. In second sheet column4 should give "YES" if all the corresponding values are exactly same. In example below "BBB" set is shown as "NO" in column4 because column3 has different values in sheet1 vs sheet2. "EEE" is shown as "NO" because this group is not available in sheet1.

    Column1 Column2 Column3
    AAA TEST1 10
    AAA TEST2 15
    AAA TEST3 20
    BBB TEST1 11
    BBB TEST2 12
    BBB TEST6 10
    CCC TEST1 6
    DDD TEST3 4
    DDD TEST4 4


    Column1 Column2 Column3 Column4 Column4
    1 AAA TEST1 10 YES
    2 AAA TEST2 15 YES
    3 AAA TEST3 20 YES
    4 BBB TEST1 11 NO
    5 BBB TEST2 12 NO
    6 BBB TEST6 13 NO
    7 CCC TEST1 6 YES
    8 DDD TEST3 4 YES
    9 DDD TEST4 4 YES
    10 EEE TEST5 3 NO
    11 EEE TEST9 2 NO

    Thanks

    Suji


    • Edited by _Suji Thursday, September 19, 2019 1:23 PM
    Thursday, September 19, 2019 1:21 PM

Answers

  • Hi Suji,

    To be sure that we're on the same page, here's an image of my Table1

    Here's an image of my Table2

    And here's the result table

    • Marked as answer by _Suji Saturday, September 28, 2019 4:56 AM
    Friday, September 20, 2019 12:38 PM

All replies

  • In the following example, I've put the tables into Excel. Names are Table1 and Table2 respectively. You will need to change references to these tables in the code to reflect your actual table names (I've highlighted these references).

    Table1 code

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        changedType = Table.TransformColumnTypes(
            Source,
            {
                {"Column1", type text}, 
                {"Column2", type text}, 
                {"Column3", type number}
            }
        )
    in
        changedType

    Table2 code

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        changedType = Table.TransformColumnTypes(
            Source,
            {
                {"Column1", Int64.Type}, 
                {"Column2", type text}, 
                {"Column3", type text}, 
                {"Column4", type number}
            }
        ),
        mergedQueries = Table.NestedJoin(
            changedType, 
            {"Column2", "Column3"},
            Table1, 
            {"Column1", "Column2"}, 
            "Table2", 
            JoinKind.LeftOuter
        ),
        expandedMergedTable = Table.ExpandTableColumn(mergedQueries, "Table2", {"Column3"}, {"Column6"}),
        groupedRows = Table.Group(
            expandedMergedTable, 
            {"Column2"}, 
            {
                "Tables", 
                (i) => 
                let 
                    listCol4 = List.Buffer(i[Column4]), 
                    listCol6 = List.Buffer(i[Column6]),
                    addedColumn = Table.AddColumn(i, "Column5", each if listCol4 = listCol6 then "YES" else "NO")
                in addedColumn,
                type table
            }
        ),
        combinedTables = Table.Combine(groupedRows[Tables]),
        removedColumn = Table.RemoveColumns(combinedTables,{"Column6"})
    in
        removedColumn

    Thursday, September 19, 2019 7:00 PM
  • Dear Colin,

    For some reason I am not getting the desired output. I am "YES" for all rows. Also Column1 in on the sheet is empty. I quite new to power query and these are the steps I followed.

    On Sheet1 selected Table1 -> Data -> New Query from Table -> Advanced editor -> copy pasted your code for Table1 -> Close and load ->  this created a new sheet3 and has same values as Table1 on sheet1

    On Sheet2 selected Table2 -> Data -> New Query from Table -> Advanced editor -> copy pasted your code for Table2 -> Close and load -> this created a new sheet4 where Column in empty for all rows and Column5 has "YES" on all rows.

    Thanks

    Suji

    Friday, September 20, 2019 3:49 AM
  • Hi Suji,

    To be sure that we're on the same page, here's an image of my Table1

    Here's an image of my Table2

    And here's the result table

    • Marked as answer by _Suji Saturday, September 28, 2019 4:56 AM
    Friday, September 20, 2019 12:38 PM