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
Unpivoted

This 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    123

From 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.