Power Query: A Changing Column Name

[andrewc]

Hi,

I am a new user of Power Query and so have a, hopefully, simple question.

I have created a query in the PQ Editor (PQE) that creates a Table in a workbook based on data in a source worksheet in another workbook.

The idea is that each month I will paste the latest month-end data over the data in the source worksheet and then update the query to refresh the Table.

A wrinkle with this is that the last column of the data in the source worksheet has as its header name the month-end date of the report. So last month it was "30-06-2020" and next month it will be "31-07-2020".

This causes a problem because when refreshing the data for 31-07-2020 the query looks for "30-06-2020" as the name for the last column and fails when it doesn't find it

Is there a dynamic way to manage in the PQE the changing header in the last column or is the solution simply manually to adjust the header in the source data?

Thanks!

[bluesky63]

Hi,

there are few ways in managing dynamic column in Power Query, Table.ColumnNames is the M code that we can create dynamic column header

actually i am not sure exactly what is your issue, you cut and paste a new data to the table for refreshing ? actually you can create a connection instead so that the table name will not be changed

Best that you upload a sample data sheets and tell us exactly what is your issue and what you want to achieve

[CK76]

There are few ways to handle it. My preference is never to use date/number string in header... but if that can't be avoided....

Use Table.ColumnNames(Source) to generate list of column names before any transformations are applied.

Then set some variable to ... = List.Last(ColumnNames)
This will supply last column name dynamically.