Powerquery pivot & unpivot columns with different contents (headers)

Question

I need to clean up a report with 45.000 lines of poorly formated sales data, that looks like this:

Col1	Col2	Col3	Col4
00/01	Week	ProductA	ProductB
00/01	1	5	5
00/01	2	5	5
00/02	Week	ProductA	ProductB
00/02	1	5	5
00/02	2	5	5
00/03	Week	ProductC	ProductB
00/03	2	5	5

The Problems:

We have multiple header lines repeating in inconsistent spacing (as sometimes a sales-week is missing for some stores), so Table.Split in fixed intervals doesnt work
When a store doesn't list a product it simple is not outputted in the report, so in store 00/03 is the only store with ProductC

I need to unpivot Col3 (and actually all product columns somehow to arrive at proper table I can work with like:

StoreNo	Week	ProductA	ProductB	ProductC
00/01	1	5	5
00/01	2	5	5
00/02	1	5	5
00/02	2	5	5
00/03	2		5	5

I looked far and wide, but either don't know enough PowerQuery M and/or don't know how to call this problem to find a proper solutions. Note: The real table has 88 columns like this with various mixed products (about 30 different products all in all)

I am so grateful to @david and horseyride (<- which you need to put on your CV ;-) ) for their answers. In the process of applying David's solution to my real world datat (88 columns), I expanded a couple of things. 1) The type change to text for all columns (regardless of how many you have in your source) ``` Table.TransformColumnTypes( Source, List.Transform( Table.ColumnNames(Source), each {_, type text}) ) ``` (More as I finalize the solution in the real world) — Leo Tulipan
– Leo Tulipan, Commented Sep 21, 2022 at 14:24

davidebacci · Accepted Answer · 2022-09-21 12:46:41Z

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDQNzBUUNJRCk9NzQbRAUX5KaXJJY5IbCelWB0klWDCFEygShhhkTAi2mwjXGYb4TLbGIvZzljNNsYwIhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col1 " = _t, #"Col2 " = _t, #"Col3 " = _t, Col4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1 ", type text}, {"Col2 ", type text}, {"Col3 ", type text}, {"Col4", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Col1 "}, {{"All", each _, type table [#"Col1 "=nullable text, #"Col2 "=nullable text, #"Col3 "=nullable text, Col4=nullable text]}}),
    Custom1 =  Table.TransformColumns(#"Grouped Rows", {{"All", each Table.PromoteHeaders(_) }}),
    #"Expanded All" = Table.ExpandTableColumn(Custom1, "All", {"Week ", "ProductA ", "ProductB", "ProductC "}, {"Week ", "ProductA ", "ProductB", "ProductC "})
in
    #"Expanded All"

horseyride · Accepted Answer · 2022-09-21 13:30:31Z

NOTE: Use David answer as the better one

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Base=({"Col1","Col2"}), //names of columns to keep
repeating_groups=1,  // then stack repeating groups of xx columns
Combo = List.Transform(List.Split(List.Difference(Table.ColumnNames(Source),Base),repeating_groups), each Base & _),
#"Added Custom" =List.Accumulate(
        Combo, 
        #table({"Column1"}, {}),
        (state,current)=> state & Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source, current)),1)
),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Column2]="Week" then [Column3] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] <> "Week")),
#"Pivoted Column1" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Custom]), "Custom", "Column3", List.Sum),
#"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column1",{{"Column2", "Week"}, {"Column1", "StoreNo"}})
in  #"Renamed Columns1"

Collectives™ on Stack Overflow

Powerquery pivot & unpivot columns with different contents (headers)

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related