Forum Discussion
Need Help! Data transformation
Hi, Could anyone please help me to transform data in desired output in Power BI. Input Data ID COL1 COL1_Value COL2 COL2_Value COL3 COL3_Value 1 ServerName ABCDEF ServerName POI...
The answers above seem tough to me...but I'm not an M guy. Just doing it using the user interface in Power Query:
1. Import data as-is
2. Remove columns ID, COL2 and COL3
3. Highlight COL1 and use TRANSPOSE feature
4. Use First Rows as Headers
Does that help?
Scott
randhir I hope this will help, i'm sure previous solution will also work but I didn't tested those.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOLSpLLfJLzE0FchydnF1c3dBFA/w9QyND0EUDw12DQiKVYnWilYyAXJfM4myojAsq1xWV6wbWYgyyraAgJzM5sSQzPw/I8/H2whDziAwFknmlOTkwKjYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, COL1 = _t, COL1_Value = _t, COL2 = _t, COL2_Value = _t, COL3 = _t, COL3_Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"COL1", type text}, {"COL1_Value", type text}, {"COL2", type text}, {"COL2_Value", type text}, {"COL3", type text}, {"COL3_Value", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"COL2", "COL3"}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Removed Columns", {"COL1_Value", "COL2_Value", "COL3_Value"}, "Attribute", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"ID"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[COL1]), "COL1", "Value"), #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Attribute"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns2", "Index", 1, 1) in #"Added Index"
