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.

user_765226 · Answer

It's a lot easier to work with columns, so I'd recommend unpivoting the Point columns, removing duplicates, and then putting it into the shape you want.

Here's a full query you can past into your Advanced Editor to look at each step more closely:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoPMEQijYAkBIHYsTrRSk5gtjGYNIGzYWpMwGqcwWxTJNIMTJqjsGNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Wire = _t, Point1 = _t, Point2 = _t, Point3 = _t, Point4 = _t, Point5 = _t, Point6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Wire", type text}, {"Point1", type text}, {"Point2", type text}, {"Point3", type text}, {"Point4", type text}, {"Point5", type text}, {"Point6", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Wire"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Wire", "Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Wire"}, {{"Point", each Text.Combine([Value],","), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Point", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Point1", "Point2", "Point3"})
in
    #"Split Column by Delimiter"

user_2872922 · Answer

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"

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

2 Answers