Forum Discussion
Transform Data on Columns with Changing Names
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!
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
2 Replies
- bolfriSolution Sage
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 - AnonymousNot applicable
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.