Mar 10 2023 07:30 PM
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
Mar 11 2023 12:24 AM
@Kailef 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.
Mar 11 2023 12:30 AM
For me that are 2 tables with 1 column each.
And the results are 2 more tables with 1 column each.
Do a left anti join from table 1 to table 2 and a left anti join from table 2 to table 1.
Mar 11 2023 01:14 AM
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
Mar 11 2023 06:08 AM
Thanks everyone. Really appreciate all the help.
Mar 11 2023 07:43 AM
@Kailef , you are welcome