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,071
Office Version
2019
Platform
Windows
If this is what you are looking for below then use the Mcode shown below it.

xl2bb.xlam
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,754
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,754
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,081,677
Messages
5,360,451
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top