Power Query - dynamic headers

[KimC2504]

Hello,
I am trying to deal with Dynamic Headers in Power Query and I have no idea how to go about it and if in fact it can be done.

I have two examples below, the Product Headers can change each time I run my report. Any suggestions how I can deal with this?

Oppty ID	Name	Number	Date	Rep	Product A	Product B	Product C
OPE-123	Fosters	A1155	31/12/2019	James	100	50
OPE-456	CUB	B1155	15/12/2019	Pat		50	100
OPE-111	Squire	A1598	15/1/2020	Tom			99
OPE-145	Boag	A1478	15/2/2020	Felix	60

​

​

​

Oppty ID	Name	Number	Date	Rep	Product B	Product D	Product C	Product E	Product G
OPE-999	CUB	B1155	1/6/2020	Tom	100	100			5
OPE-874	Squire	A1598	15/1/2020	Tina		50		11
OPE-144	Fosters	A1155	11/1/2020	Tiny		50	50		50

 

[macfuller]

What are you trying to do with the variable columns? Do you simply want to delete them? Are you unpivoting into a generic "Product" column? Do the product names all come from a "master" list where there can't be any more than what's in the list, or can they be anything?

 

[KimC2504]

Yes I want to unpivot them into a generic product column.

The products all come from a master list where they are limited.

 

[alansidman]

If this is what you are looking for below then use the Mcode shown below it.

Book1
	A	B	C	D	E	F	G
1	Oppty ID	Name	Number	Date	Rep	Attribute	Value
2	OPE-123	Fosters	A1155	31/12/2019	James	Product A	100
3	OPE-123	Fosters	A1155	31/12/2019	James	Product B	50
4	OPE-456	CUB	B1155	15/12/2019	Pat	Product B	50
5	OPE-456	CUB	B1155	15/12/2019	Pat	Product C	100
6	OPE-111	Squire	A1598	15/1/2020	Tom	Product C	99
7	OPE-145	Boag	A1478	15/2/2020	Felix	Product A	60
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Oppty ID", type text}, {"Name", type text}, {"Number", type text}, {"Date", type text}, {"Rep", type text}, {"Product A", Int64.Type}, {"Product B", Int64.Type}, {"Product C", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Oppty ID", "Name", "Number", "Date", "Rep"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

 

[KimC2504]

Hi Alan,
that works fine for the first table but my problem is next time I run the report, it will be the second example and the power query wants to see Product A and won't pick up the new columns.
Sometimes all product columns will be in the table and other times only a subset of Product columns will be in the table

 

[sandy666]

remove line #"Changed Type"...
and change in this line #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, ...

I assume the first five columns are always the same

 

[KimC2504]

Hi Sandy666,
Is this what you mean?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source,{{"Oppty ID", "Name", "Number", "Date", "Rep"}, "Attribute", "Value"})
in
    #"Unpivoted Columns"

I get an expression error

First five columns are always the same

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Oppty ID", "Name", "Number", "Date", "Rep"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

you did something what I didn't say
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source,{{"Oppty ID", "Name", "Number", "Date", "Rep"}, "Attribute", "Value"})

 

[KimC2504]

Thank you Sandy666, that is awesome!

 

[sandy666]

you are welcome
have a nice day