Power Query: Source Data with Variable Column Header

Graemea

Board Regular
Joined
Oct 30, 2015
Messages
90
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have created a connection using Power Query to a worksheet which is used to populate a pivot table.

Each month I will paste over the data in the worksheet and refresh connection to update the pivot table.

One problem is that the heading of one of columns in the worksheet changes each month to show the date of the latest month-end (formatted as text). For example, in this month's report the 4th column will have the header "31-08-2020" and next month it will be "30-09-2020".

This causes the query to fail when I try to refresh it.

I would want the resulting pivot table to include a field with the name of the 4th column (so "31-08-2020" this month and "30-09-2020" when I refresh it next month).

Is there a way around this other than manually to adjust the column header in the worksheet to be something that doesn't change each month?

Thanks!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,797
try
= Table.RenameColumns(previous_step,{{Table.ColumnNames(previous_step){3}, "Period"}})
 

Graemea

Board Regular
Joined
Oct 30, 2015
Messages
90
Office Version
  1. 365
Platform
  1. Windows
Thanks for the quick response!

Can I please ask how I would apply this to the M code in the attached image? I would like it to apply to both columns 3 and 4 (with headers "30 June 2020" and "31 July 2020".

Thanks! PQ_Col_Variable_Header.PNG
PQ_Col_Variable_Header.PNG
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,797
could you post M code not a picture?
use
[CODE=pq]
code here
[/CODE]
 

Graemea

Board Regular
Joined
Oct 30, 2015
Messages
90
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry, I've pasted the code in (I wasn't sure what you meant by
Power Query:
 so hopefully this is ok:

[CODE=pq] 
let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Code", type text}, {"Product Name", type text}, {"30 Jun 2020", Int64.Type}, {"31 Jul 2020", type any}})

in

#"Changed Type"
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,797
;)
should be
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table21"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Code", type text}, {"Product Name", type text}, {"30 Jun 2020", Int64.Type}, {"31 Jul 2020", type any}}),
    Ren = Table.RenameColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){3}, "PizzaHut"}})
in
    Ren
change PizzaHut to proper name

btw. you should wrap code only by code tags not a whole text in the post
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,797

ADVERTISEMENT

or if you want end of month you can try this
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Code", Int64.Type}, {"Product Name", Int64.Type}, {"30 Jun 2020", Int64.Type}, {"31 Jul 2020", Int64.Type}}),
    Ren = Table.RenameColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){3}, ""&Text.From(Date.EndOfMonth(Date.From(DateTime.LocalNow())))&""}})
in
    Ren
and it will give you
Sales CodeProduct Name30 Jun 202030/09/2020
 

Graemea

Board Regular
Joined
Oct 30, 2015
Messages
90
Office Version
  1. 365
Platform
  1. Windows
Thanks very much for all of the help!
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,797
You are welcome & thanks for the feedback
Have a nice day :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,413
Messages
5,528,625
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top