Forum Discussion
Import folder with files that has different headers and still keep the different header
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
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
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
3 Replies
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
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)
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
