Forum Discussion
How to handle data source column headers changing?
Hi,
Each month I recieve a .csv file where each row represents a material that we order from a supplier and columns 7 and onwards represent the forcast for each month, starting with the current month. Example below.
| Material | Description | Uom | Vendor Material | Warehouse | Ord Type | Jun-21 | Jul-21 |
| 8000023T/SEAL | EA | On Order | 120 | 0 | |||
| 8000023T/SEAL | EA | Planned | 0 | 200 | |||
| A41-1L | 1L PIS POS TT | EA | On Order | 0 | 100 |
Next month I will get a new file that starts with 'Jul-21'. I would like to make it so any visuals I create using this data can handle the change and still accurately display the column headers.
An ideal solution for me would be a way to refer to a column in a query by column number instead of by name in DAX, but any other workarounds would be much appreciated.
Hi Sam_Bolsover ,
It is suggested to unpivot two column in Power Query, then select the attribute field for you title.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi Sam,
In Power Query, I'd be inclined to select all of the columns that are consistent, right click and choose 'Unpivot other columns' as one of the first steps. This will give you the consistency you need to work with in your measures.
Regards,
Kim
Hi Sam_Bolsover ,
It is suggested to unpivot two column in Power Query, then select the attribute field for you title.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Stephen,
I think this approach can get me what I want if display the data as a matrix instead of a table.
Best regards,
Sam Bolsover
As I suggested, you're going to want to choose 'Unpivot other columns' if you want the solution to be dynamic. If you choose unpivot columns it will break when a new month of data comes in.
Hi,
If on July 4, 2021, you receive a file which starts with Jul-21, then will this file replace the file which you received in Jun-21? If yes, then how will you get access to the Jun-21 figures when it is Jul-21?
Hi Ashish,
That is correct. This report is solely for displaying forcast data and so information from prior months is not needed.
Best regards,
Sam Bolsover
Hi,
See if the technique shown here takes into account new columns that you add daily - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks.
