Power Query: Source Data with Variable Column Header

[Graemea]

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!

 

[sandy666]

try
= Table.RenameColumns(previous_step,{{Table.ColumnNames(previous_step){3}, "Period"}})

 

[Graemea]

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!

 

[sandy666]

could you post M code not a picture?
use
[CODE=pq]
code here
[/CODE]

 

[Graemea]

Sorry, I've pasted the code in (I wasn't sure what you meant by

 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]

should be

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]

or if you want end of month you can try this

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 Code	Product Name	30 Jun 2020	30/09/2020

 

[Graemea]

Thanks very much for all of the help!

 

[sandy666]

You are welcome & thanks for the feedback
Have a nice day