Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous,

Please try the transformation below:

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",{"ID"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ServerName", type text}, {"DiskName", type text}, {"Application", type text}}),
    #"Removed Alternate Rows" = Table.AlternateRows(#"Changed Type1",1,1,1),
    #"Added Index" = Table.AddIndexColumn(#"Removed Alternate Rows", "ID", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"ID", "ServerName", "DiskName", "Application"})
in
    #"Reordered Columns"