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"
That's how it would look once you create two queries with the codes that I provided you.Table 2 is the desired output.
@Anonymous just wondering if you tried the code I posted.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
HI, @Anonymous
You may try this way:
Duplicate the basic table twice.
Then remove COL1 and COL1_Value for first table, and remove COL2 and COL2_Value for second table, and remove COL3 and COL3_Value for yhird table
Then merge table1 with table2 by ID and COL column, expend then merge with table3 again by the same logic.
Now we get the COL2 and COL3 values are in order same as COL1
Then transform it as above.
Best Regards,
Lin
hi, @Anonymous
You could close "Enable load" for other queries.
For example:
Then they won't load in data model.
Best Regards,
Lin
@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"
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Helpful resources
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
