Remove Duplicate Rows and Change unique Columns to Additional Columns based on Duplication

Hi Anonymous , I also had this issue, did you find any solutions elsewhere?

Hi Anonymous  and Anonymous ,

It's a common issue. The video below provides a workaround.

Power Query: How to collapse Multiple Rows to a Single Row - YouTube

For Anonymous 's data, below is my M codes, you can copy and paste it into the advanced editor in Power Query for details. Or you can directly download my attachment for details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKSi3LLM7Mz1PwK81NSi0CioQl5pSmAumQyoJUpVidaCVHIMcQiA30QGRAUWZuYlElmowRkAxOTc7PS4HJOQFFjMByxmi6EDImqLpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Revision Number", Int64.Type}, {"Value", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Type]&" Value"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Type]), "Type", "ID"),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Custom]), "Custom", "Value", List.Sum),
    #"Duplicated Column" = Table.DuplicateColumn(#"Pivoted Column1", "Revision Number", "Revision Number - Copy"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Duplicated Column", {"Revision Number - Copy"}, "Attribute", "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type2"),
    #"Pivoted Column2" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column2"

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.