Remove Duplicate Rows and Change unique Columns to...

sid-poly · ‎06-14-2023

There is a table which has to be Changed in the Power Query with duplicate rows where few columns with unique values have to be pushed into new columns. Please find the table below for your reference

ID	Revision Number	Value	Type
A	1	0.1	Primary
A	1	0.2	Secondary
B	2	0.3	Primary
B	2	0.4	Secondary

This has to be in the output format.

Primary	Secondary	Revision Number	Value 1	Value 2
A	A	1	0.1	0.2
B	B	2	0.3	0.4

Need help to implement the same in the Power Query

Anonymous · ‎06-18-2023

Hi @sid-poly 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 @sid-poly '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.