Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi community! 🙂
I'm wondering if you can help me out with the following issue.
I have to deal with a system generated excel which is unfortunately kind of a mess.
The first column "art nr" contains all needed article numbers. The following columns have no header; however the structure is like that: calendar week -> quantity to be delivered in the respective calendar week -> calendar week -> quantity to be delivered in the respective calendar week -> ...
I am now looking for a way to restructure the table (preferably with PowerQuery).
The desired output should preferably look somehow like this:
Do you guys see a way to restructure the table like shown above?
Preferably I would like to have the article numbers in the first column.
Followed by the calendar weeks with the respective quantity.
(If possible it would be great if only columns/calendar weeks with a quantity would be shown.)
I figured out a manually way by combining 3 excel formulas:
However I am now looking for a more automated way in doing it - preferably with PowerQuery.
Maybe you can help me?
Looking forward to hearing from you and thanks in advance for your help & support 🙂
Solved! Go to Solution.
Below is the required code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {Table.ColumnNames(#"Promoted Headers"){0}}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each try if Text.Contains([Value],"/") then [Value] else null otherwise null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom1", each try if not Text.Contains([Value],"/") then [Value] else null otherwise [Value]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Value"}),
#"Filled Up" = Table.FillUp(#"Removed Columns1",{"Custom1"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom] <> null)),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows", "ColumnForSort", each Text.Middle([Custom],Text.PositionOf([Custom],"/")+1)&Text.Middle([Custom],0,Text.PositionOf([Custom],"/"))),
#"Sorted Rows" = Table.Sort(#"Added Custom2",{{"ColumnForSort", Order.Ascending}}),
#"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"ColumnForSort"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Custom]), "Custom", "Custom1")
in
#"Pivoted Column"
The corresponding Excel can be downloaded from https://1drv.ms/x/s!Akd5y6ruJhvhug-oiBrwMvll7bhm?e=YuXZAV
Below is the required code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {Table.ColumnNames(#"Promoted Headers"){0}}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each try if Text.Contains([Value],"/") then [Value] else null otherwise null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom1", each try if not Text.Contains([Value],"/") then [Value] else null otherwise [Value]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Value"}),
#"Filled Up" = Table.FillUp(#"Removed Columns1",{"Custom1"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom] <> null)),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows", "ColumnForSort", each Text.Middle([Custom],Text.PositionOf([Custom],"/")+1)&Text.Middle([Custom],0,Text.PositionOf([Custom],"/"))),
#"Sorted Rows" = Table.Sort(#"Added Custom2",{{"ColumnForSort", Order.Ascending}}),
#"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"ColumnForSort"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Custom]), "Custom", "Custom1")
in
#"Pivoted Column"
The corresponding Excel can be downloaded from https://1drv.ms/x/s!Akd5y6ruJhvhug-oiBrwMvll7bhm?e=YuXZAV
Great!
Much appreciated your help - works perfectly fine and suits all my needs!
Thanks a lot 🙂
Check out the July 2025 Power BI update to learn about new features.