Power Query - dynamic headers

KimC2504

Board Regular
Joined
Jul 17, 2012
Messages
131
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 IDNameNumberDateRepProduct AProduct BProduct C
OPE-123FostersA115531/12/2019James10050
OPE-456CUBB115515/12/2019Pat50100
OPE-111SquireA159815/1/2020Tom99
OPE-145BoagA147815/2/2020Felix60




Oppty IDNameNumberDateRepProduct BProduct DProduct CProduct EProduct G
OPE-999CUBB11551/6/2020Tom1001005
OPE-874SquireA159815/1/2020Tina5011
OPE-144FostersA115511/1/2020Tiny505050
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
251
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

Board Regular
Joined
Jul 17, 2012
Messages
131
Yes I want to unpivot them into a generic product column.

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

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,076
Office Version
2019
Platform
Windows
If this is what you are looking for below then use the Mcode shown below it.

Book1
ABCDEFG
1Oppty IDNameNumberDateRepAttributeValue
2OPE-123FostersA115531/12/2019JamesProduct A100
3OPE-123FostersA115531/12/2019JamesProduct B50
4OPE-456CUBB115515/12/2019PatProduct B50
5OPE-456CUBB115515/12/2019PatProduct C100
6OPE-111SquireA159815/1/2020TomProduct C99
7OPE-145BoagA147815/2/2020FelixProduct A60
Sheet2


VBA Code:
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

Board Regular
Joined
Jul 17, 2012
Messages
131
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,857
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

Board Regular
Joined
Jul 17, 2012
Messages
131
Hi Sandy666,
Is this what you mean?


VBA Code:
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
 

Attachments

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,857
Code:
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"})
 

Forum statistics

Threads
1,082,610
Messages
5,366,601
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top