Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
dannecr
Regular Visitor

INDEX MATCH - create dynamic matrix table based on unsorted table

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

input table.png

  • The amount of lines & columns may change; deepending on how many articles you want to look at & how many weeks you would like to have a forecast in the future.
  • The calendar weeks are not limited to the current year but may also include weeks of the current and the upcoming year.

 

I am now looking for a way to restructure the table (preferably with PowerQuery).

The desired output should preferably look somehow like this:

desired output.png

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:

  • IFERROR
  • INDEX
  • MATCH

 

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 🙂

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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 

View solution in original post

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

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 🙂

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.