Forum Discussion
Need Help! Data transformation
You need to create a query called Table4 with the following code for the output to execute
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKkst8kvMTVWK1YlWcskszoZzHAsKcjKTE0sy8/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Keywords to be searched for" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Keywords to be searched for", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Keywords to be searched for", "Keywords to be searched for"}})
in
#"Renamed Columns"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
Table4 is created to replicate this -
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"
