Forum Discussion
Unpivot data before expand/append
Hi Anonymous ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you RicoZhou Ilgar_Zarbali for the prompt response.
1. Handling 50+ Columns Without Hardcoding
You are right - hardcoding column names won’t scale. To handle any number of columns (even future ones), use:
let
Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
// Replace "YourTableName" with your actual table or range name
Unpivoted = Table.UnpivotOtherColumns(Source, {"Block"}, "Week", "Value")
in
UnpivotedThis automatically unpivots all columns except "Block", no matter how many there are or how often they change.
2. "Looping Through Each Block" Automatically
You don’t need a manual loop. Power Query is already row-wise by design.
Once unpivoted as shown above, your data will look like expected for example:
Block Week Value
A Week 1 123From here, you can:
1.Group by Block
2.Pivot Week if needed
3.Or analyze it as a clean, flat table
So there's no need to loop manually.Power Query handles that for every block and week combo automatically.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you.
Hi Anonymous ，
If you can ensure that there are two rows between the weekly data like your screenshot, you can try this workaround.
Sample:
M Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk9NzTZU0lFCQbE60UoBjkBWgBOIcAYRLiDCFSZraQlkYSPAstjMwyYGstyIgOVwewPcQIQ7fuuBBKkOMCbgAA8Q4Ykkh9XbILlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Week", each if Text.Contains([Column1], "Week") then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Week"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column1], "Week") and [Column1] <> ""),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Week"}, "Attribute", "Value"),
#"Added Custom1" = Table.AddColumn(#"Unpivoted Other Columns", "Parameter", each if Text.Contains([Value], "P") then [Value] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each let _Week = [Week], _Attribute = [Attribute] in Table.SelectRows(#"Added Custom1", each [Week] = _Week and [Attribute] = _Attribute and [Parameter] <> [Value]) [Value]),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Flag", each if [Value] <> [Custom] then [Custom] else null),
#"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column", each ([Flag] = "99")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute", "Value", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Flag", "Value"}})
in
#"Renamed Columns"Result is as below.
Best Regards,
Rico Zhou
Sorry but it seems this part speficied the number of column but my data has over 50 columns and cant expect how many more in the future.
[Serialized.Text = true]