Excel 365 Power Query Help extracting unique data

Question

So I have already set up a power query in a sharepoint folder to pull data from a specific sheet.

I have "Column 1" and "Column 2". Each column contains a list of names. Some of the names will appear in both while some names may only be in Column 1 and some different names may be in Column 2.

I need to extra the unique names out of Column 1 that are not in Column 2 and the unique names that are in Column 2 and not in Column 1.

So for example:

Column 1

John Doe

Jane Smith

Column 2

John Doe

Alex Keaton

Using Power Query, I need the steps that will create two new columns. Column 3 will contain unique names from Column 1 that are not in Column 2. Column 4 will contain unique names from Column 2 that's not in Column 1.

So for example, the result would be:

Column 3:

Jane Smith

Column 4:

Alex Keaton

sergeibaklan · Answer

Kailef&nbsp;
As variant that could be manual script like
let
    Source = Excel.CurrentWorkbook(){[Name="tblNames"]}[Content],
    Column1 = Source[Column1],
    Column2 = Source[Column2],
    Column3 = List.RemoveMatchingItems( Column1, Column2 ),
    Column4 = List.RemoveMatchingItems( Column2, Column1 ),
    /*
        if only unique values to keep, above is like
        Column3 =  List.Distinct(
            List.RemoveMatchingItems( Column1, Column2 ) ),
        Column4 =  List.Distinct(
            List.RemoveMatchingItems( Column2, Column1 ) ),

*/

// Both result1 and result 2 return the same,
    // select one on your choice

result1 = Table.FromColumns(
        {Column1, Column2, Column3, Column4},
        {"1st", "2nd", "Only 1st", "Only 2nd"}
         ),
    
    result2 = #table(
        type table
            [
                1st = text,
                2nd = text,
                #"Only 1st" = text,
                #"Only 2nd" = text
            ],
        List.Zip( {Column1, Column2, Column3, Column4} )
      )
in
    result2
which returns

&nbsp;

riny_van_eekelen · Answer

Kailef&nbsp;Probably many other ways to achieve this, but the attached file contains a 'quick-and-dirty' solution that returns two separate tables. On for all unique names only in the 1st and another for unique names only in the 2nd.&nbsp;

detlef_lewin · Answer

Kailef&nbsp;For me that are 2 tables with 1 column each.And the results are 2 more tables with 1 column each.&nbsp;Do a left anti join from table 1 to table 2 and a left anti join from table 2 to table 1.&nbsp;

kailef · Answer

Thanks everyone. Really appreciate all the help.&nbsp;&nbsp;

sergeibaklan · Answer

Kailef&nbsp;, you are welcome

Forum Discussion

Excel 365 Power Query Help extracting unique data

5 Replies

Resources