How to handle data source column headers changing?

[Sam_Bolsover]

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. 

[v-stephen-msft · Accepted solution]

Hi @Sam_Bolsover ,

It is suggested to unpivot two column in Power Query, then select the attribute field for you title.

Reference:https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882... 

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-format-visual-titles#se...

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.

[Sam_Bolsover]

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

[KNP]

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. 

[Sam_Bolsover]

Thank you Kim, I will keep this in mind.

Best regards,

Sam Bolsover

[KNP]

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

[Ashish_Mathur]

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?

[Sam_Bolsover]

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

[Ashish_Mathur]

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.