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...
Hi @smpa01,
Thanks for your time and efforts. This is what I am looking for a dynamic approach. Actually I have total total 40 columns ( COL1,COL_Value, COL2,COL2_Value.... COL20, COL20_Value) .
Your solution is working fine as per sample data posted on web.
But still I am not able to understand how TABLE4 works.
It would be really great if you can explain me working of TABLE4.
Sorry,I am new to Power BI, could you please consider the table below as your Excel source and provide me query.
I'll modify your query for others remaining columns and try at my real data. Please note, given below sample data is containing all the possible scenerios.
| ID | COL1 | COL1_Value | COL2 | COL2_Value | COL3 | COL3_Value | COL4 | COL4_Value | COL5 | COL5_Value |
| 1 | ServerType | str6val | Application | str12val | Metric | str18val | Perimeter | str22val | SubServerType | str1val |
| 2 | ServerType | str7val | ServerType | str13val | Metric | str19val | Perimeter | str23val | SubServerType | str2val |
| 3 | ServerName | str8val | ServerName | str14val | Application | str20val | Metric | str24val | SubServerType | str3val |
| 4 | ServerName | str9val | ServerName | str15val | ServerType | str21val | Application | str25val | Perimeter | str4val |
| 5 | ServerName | str10val | ServerName | str16val | Metric | str5val | ||||
| 6 | DiskName | str11val | ServerName | str17val | Metric | str6val |
Thanks,
Randhir
Hi,
This M code generates the table below
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"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}, {"COL4", type text}, {"COL4_Value", type text}, {"COL5", type text}, {"COL5_Value", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
#"Uppercased Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", Text.Upper, type text}}),
#"Added Custom" = Table.AddColumn(#"Uppercased Text", "Custom", each if Text.End([Attribute],5)="VALUE" then [Value] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if Text.End([Attribute],5)="VALUE" then null else [Value]),
#"Filled Down1" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([Custom] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Value"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom.2", each Number.ToText([ID])&[Custom.1]),
Partition = Table.Group(#"Added Custom2", {"Custom.2"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ID", "Custom", "Custom.1", "Index"}, {"ID", "Custom", "Custom.1", "Index"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Partition",{"Custom.2"}),
#"Added Custom3" = Table.AddColumn(#"Removed Columns1", "Custom.2", each [Custom.1]&Number.ToText([Index])),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom3",{"Custom.1", "Index"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns2",{{"ID", Order.Ascending}, {"Custom.2", Order.Ascending}}),
#"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Custom.2]), "Custom.2", "Custom")
in
#"Pivoted Column"