On duplicate rows of first column move data in other columns leaving only unique values in first column

Power Query
21

Great challenge and amazing solution @Melissa

This is my try with 99% using UI.

let
    Source = ProdOrders,
    UnpivotedOther = Table.UnpivotOtherColumns(Source, {"Original Order"}, "Attribute", "Value"),
    RemovedDup = Table.Distinct(UnpivotedOther, {"Original Order", "Value"}),
    Sort = Table.Buffer(Table.Sort(RemovedDup,{{"Original Order", Order.Ascending}, {"Value", Order.Ascending}})),
    Group = Table.Group(Sort, {"Original Order"}, {{"Count", each Table.AddIndexColumn(_,"i",1,1,type text) }}),
    Expand = Table.ExpandTableColumn(Group, "Count", {"Value", "i"}, {"Value", "i"}),
    Prefix = Table.TransformColumns(Expand, {{"i", each "Rework Order-" & Text.From(_, "en-GB"), type text}}),
    Pivot = Table.Pivot(Prefix, List.Distinct(Prefix[i]), "i", "Value")
in
    Pivot
2 Likes
22

@Melissa Thanks so much again for your efforts, really appreciate the engagement from the community.

I changed all columns to type text by adding the last line of m-Code

let
Source = Table1,
Result = Table.Combine( Table.Group( Source, {“Original Order”}, {{“All”, each
let
a = List.Distinct( List.RemoveMatchingItems( List.Combine( Table.ToColumns()), {null, “”})),
b = {[Original Order]{0}} & List.Sort( List.RemoveFirstN( a, 1 )),
c = Table.FromRows( {b}, {“Original Order”} & List.Transform( {1…List.Count(a)-1 }, each “Rework Order-” & Text.From()))
in c }} ) [All] ),
#“Changed Type” = Table.TransformColumnTypes(Result, List.Transform(Table.ColumnNames(Result), each {_, type text}))
in
#“Changed Type”

Best Regards
J

2 Likes
23

You are a magician, great stuff @jprlimey

1 Like