INDEX MATCH - create dynamic matrix table based on unsorted table

[dannecr]

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

• 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:

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 🙂

[Vijay_A_Verma · Accepted 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 

[dannecr]

Great!
Much appreciated your help - works perfectly fine and suits all my needs!

Thanks a lot 🙂