Forum Discussion

basicquestions's avatar
basicquestions
Frequent Visitor
3 years ago

Column Names Change by Month, which throws off PowerQuery. How do I handle this?

Greetings,   I hope y'all are all doing well!   I've got a quick question. I've got a file in which the column names change by month. That is, each month, the column names. For example, last mont...
Need Help
ronrsnfld's avatar
ronrsnfld
Super User
3 years ago

There are many ways to refer to columns that change their names.  In general, you start with a List of the column names.  What you do with that list depends on what your code is doing.

 

For example, imagine

  • Column 1-2 are dates named `Start Date` and `End Date`
  • columns 3-7 headers are variably named and contain integers
  • You want to set the data types

Code like below should do that:

ypes = {{"Start Date", type date}} & {{"End Date", type date}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),2), each {_, Int64.Type}),

#"Changed Type" = Table.TransformColumnTypes(Source, types)

 

You would use slightly different methods for other functions, but you can get away from having to hard code column headers with variations on this.