Transform Data on Columns with Changing Names

[Nelgnat]

Hi there,

I have an Excel spreadsheet which exports from another source. We will export the Excel file to the same location every time, then refresh the report. I need to Change the Type of some columns, but the column names from one export to the next will change. There are some columns which are named the same every time. Is there a way to identify the columns I want Power BI to change the type for other than manually selecting the Columns by name?

For example, my Excel column headers in today's export have the following names, with an example of the values that would follow.

Project Name	June 2023	September 2023	February 2024
Apple Project Inc.	56.5	32.4	18.7
Banana Project Ltd.	12.1	9.7	30.0

Next month when we export it, the Excel column headers which contain the dates will likely be different. It does not always shift by exactly one month though, as shown in my example. It could be any month or year in the future.

Project Name	September 2023	February 2024	March 2024
Apple Project Inc.	32.4	18.7	23.5
Banana Project Ltd.	9.7	30.0	10.2

Regardless of which month or year it is, I need those columns to have a data type of Fixed Decimal. I do not want to manually choose which columns Power BI updates every time we refresh.

Thank you for any help you can offer!

[Nelgnat]

Bolfri,

Thank you so much!

An added layer of complexity is that there are multiple columns with the same name (July 2023 for example), but one of them contains values for hours, and the other contains values for dollars.

I am working with my team to export this information in a different way so we can leverage your solution.

[bolfri · Accepted solution]

Hi, just unpivot those columns to a single column and change the type.

https://learn.microsoft.com/en-us/powerquery-m/table-unpivotothercolumns

You will receive something like this:

Project Name	Month	Value
Apple Project Inc.	June 2023	56.5
Apple Project Inc.	September 2023	32.4
Apple Project Inc.	February 2024	18.7
Banana Project Ltd.	June 2023	12.1
Banana Project Ltd.	September 2023	9.7
Banana Project Ltd.	February 2024	30.0