Forum Discussion
Row into columns for value
Hi,
i have this data:
i have 4 rows for the same WTPARTNUMBER, the only different value is the column "MDP_PLM_Superse"
I want to transoform this into 1 row and X new columns based on "MDP_PLM_Superse".
I've tried with power query "pivot column" and "not aggregate" but it return me "compex data type" error.
Is this possible?
Thanks
Hi Anonymous ,
Can this help?
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
can you demonstrate what you are expecting and how many columns it creates?
In SQL Server you can use the PIVOT function to transform the data from rows to columns: select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from yourtable ) d pivot ( max(value) for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber) ) piv;
Duplicate your last column. Select Pivot column on this new column and select for values your original last column. Don't aggregate.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSspJzFDSUcrPSwWSJeX5IDKjKBXEM1SK1cGrwIiQAmNCCkyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column5", "Column5 - Copy"),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Duplicated Column", {{"Column5 - Copy", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Duplicated Column", {{"Column5 - Copy", type text}}, "en-US")[#"Column5 - Copy"]), "Column5 - Copy", "Column5")
in
#"Pivoted Column"please demonstrate what you are trying to do with an example of what you are expecting as this isn't very clear.
