How do I remove duplicates WITHIN a row using Power Query?

Question

I have data that looks like this:

Wire	Point1	Point2	Point3	Point4	Point5	Point6
A	WP1	WP1	WP2			WP2
B	WP3	WP4	WP3			WP4
C	WP5	WP5	WP6	WP7	WP6	WP7

(note the varying lengths of each row, and the duplicates)

I would like to have the end result be:

Wire	Point1	Point2	Point3
A	WP1	WP2
B	WP3	WP4
C	WP5	WP6	WP7

Duplicates removed, and blank spaces removed.

This would be VERY similar to the =UNIQUE() function, but that is not available in power query.

Answer 1

• Unpivot
• Group by Wire
  • Aggregate into sorted List of Unique Points
• Calculate Max number of items in all the Lists to use in the later Column Splitter
• Extract the List of points into semicolon separated string
• Split into new columns

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, Text.Type})),

//Unpivot
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Wire"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),

//Group by Wire
//Aggregate by sorted, unique list of Points for each Wire
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Wire"}, {
        {"Point", each List.Sort(List.Distinct([Value]))}}),

//Calculate the Max unique Points for any Wire (for subsequent splitting
maxPoints = List.Max(List.Transform(#"Grouped Rows"[Point], each List.Count(_))),

//Extract the List values into a semicolon separated list
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", 
        {"Point", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

//Then split into new columns using the semicolon delimiter
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Point", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),maxPoints)
        
in 
    #"Split Column by Delimiter"