Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 🙂
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
10 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |