Forum Discussion

purple_SP's avatar
purple_SP
Helper I
3 years ago
Solved

Finding duplicate lists where the values are in different orders

Hello, I am working with some data in the following type of format where column 3 is a list of values:   Column 1  Column 2  Column 3  John  Smith  A,B Sarah  Jones  B,C Jane  Wo...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    NewStep=Table.Distinct(Table.TransformColumns(PreviousStepName,{"Column 3",each Text.Combine(List.Sort(Text.Split(_,",")),",")}))

6677028's avatar
6677028
Frequent Visitor
3 years ago

You check 

let

    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    #"Sorted Rows" = Table.Sort(#"Promoted Headers",{{"Col1", Order.Ascending}}),

    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),

    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),

    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.FullOuter),

    #"Sorted Rows1" = Table.Sort(#"Merged Queries",{{"Index", Order.Ascending}}),

    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Sorted Rows1", "Added Index1", {"Col1"}, {"Added Index1.Col1"}),

    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Custom", each if [Col1]=[Added Index1.Col1] then null else [Col1]),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Added Index1.Col1", "Col1"}),

    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Col2", "Col3"}),

    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Custom] <> null))

in

    #"Filtered Rows"