Import folder with files that has different headers and still keep the different header

[PatrickWong]

I would appreciate some help here. my problem

every month, I will get a excel file drop into the folder. it would contain the file name forecast-current month.xlsx in MMYYYY format like forecast-022023.xlsx 

within the file, it would 1 tab and has 7 columns

description, current month (in DDMMYY) and the next 5 month. eg 

Description, 01/02/23, 01/03/23, 01/04/23, 01/05/23, 01/06/23, 01/07/23

I can import the multiple files successfully but the header column will take on the current month + the next 5month of the transform sample file. so the file 

forecast-0202023.xlsx will have Description, 01/02/23, 01/03/23, 01/04/23, 01/05/23, 01/06/23, 01/07/23

and 

forecast-0203023.xlsx will have Description, 01/02/23, 01/03/23, 01/04/23, 01/05/23, 01/06/23, 01/07/23

 when it should be

forecast-0203023.xlsx will have Description, 01/03/23, 01/04/23, 01/05/23, 01/06/23, 01/07/23, 01/08/23

How can I get htis corrected. Much Thanks in advance

[PhilipTreacy · Accepted solution]

Hi @PatrickWong 

Not sure what you have in mind when you ask for it to be 'corrected'. 

The best way to fix this would be to have the data sent to you in the proper format - tabular data - i.e. not using dates as column headers, but having a Date column and a Value column.

But given what you have, and starting with data like this

Right click the column header for the Description column and Unpivot other columns to give this

Regards

Phil

[PhilipTreacy · Accepted solution]

Hi @PatrickWong 

Here's an example PBIX file

You should be selecting the Description column then unpivoting other columns.  That way the 'other' column names aren't explicitly mentioned in the query so should work for all files.

Regards

Phil

[PhilipTreacy · Accepted solution]

Hi @PatrickWong 

Not sure what you have in mind when you ask for it to be 'corrected'. 

The best way to fix this would be to have the data sent to you in the proper format - tabular data - i.e. not using dates as column headers, but having a Date column and a Value column.

But given what you have, and starting with data like this

Right click the column header for the Description column and Unpivot other columns to give this

Regards

Phil

[PatrickWong]

Hi PhilipTreacy

Can you share the pbix file so that I can understand how it is to be done, 

If I transform to Tabular format in the transformation sample file and it works great for the first file, but as PBI convert the second file, it cannot find the name of the first column (which now has the name of the second column)