Forum Discussion
Impactful IndividualChange of sample file for folder load got error/ promote headers alternative
Hello,
I'm uploading multiple csv files to my report as a folder. Unfortunately all tables in there are not really table formatted. I'm using the "sample file" - function to make changes for all unformatted files. The first change I do is to extract the creation date somwhere in the first row and to put it into every row of a new custom column. And delete all empty rows after, until I reach to the top of the column headers. Afterwards it looks like this:
| 0 | Column1 | Column2 | Custom | |||
| 1 | OrderNo. | Stock | 02/02/2022 | |||
| 2 | 1 | 123131 | 02/02/2022 | |||
| 3 | 2 | 13212 | 02/02/2022 | |||
| 4 | 3 | 767 | 02/02/2022 | |||
| 5 | 4 | 55 | 02/02/2022 | |||
| 6 | 5 | 77 | 02/02/2022 | |||
| 7 | 6 | 66 | 02/02/2022 | |||
| 8 | 7 | 55 | 02/02/2022 | |||
| 9 | 8 | 5757 | 02/02/2022 | |||
| 10 | 9 | 55 | 02/02/2022 |
Following method is strangely not working out:
If I first do the "promote headers" button and change the now promoted "02/02/2022" from the custom column into the actual name "Date" as header I get errors with all other *csv tables to come.
But if I just rename every column manually and delete the first row with the column names everything works fine.
Can someone explain me why error occurs?
And since in reality I have many of columns to rename manually, it is a very tedious thing to do. Is there a way to deal with it, while still using "promote headers" at one point?
Thank you very much in advance.
Best.
Hi, e problem is that, once promoted, you try to change "02/02/2022" but it is not the same in all csv files.
To solve this before promoting you have to add a conditional column [Custom1]
if [Column1] = "OrderNo." then "Date" else [Column1]
(the use of " depends on how you create the column but the result has to be something like = Table.AddColumn(Source, "Custom1", each if [Column1] = "OrderNo." then "Date" else [Column1])
Next you delete [Custom] and promote header.
Now you can import all af your csv files
If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !
5 Replies
- serpiva64
Solution Sage
Hi, e problem is that, once promoted, you try to change "02/02/2022" but it is not the same in all csv files.
To solve this before promoting you have to add a conditional column [Custom1]
if [Column1] = "OrderNo." then "Date" else [Column1]
(the use of " depends on how you create the column but the result has to be something like = Table.AddColumn(Source, "Custom1", each if [Column1] = "OrderNo." then "Date" else [Column1])
Next you delete [Custom] and promote header.
Now you can import all af your csv files
If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !
Applicable88serpiva64 thank you very much.
I thought that all files will automatically use their own dates as column name. But of course that what you describe makes sense, since every literal change will carry on to all *csv files.
Thank you very much.
Have a nice weekend.
Best.
- Applicable88
serpiva64 , to get the custom column correctly, do you maybe mean:
if [Column1] = "OrderNo." then "Date" else [Column3]
instead of:
if [Column1] = "OrderNo." then "Date" else [Column1]
Otherwise I would get the rows of column 1 instead of the Date cells into the rows.
- serpiva64
Sorry, You are right, it was an error while copyng the line.
v-kkf-msftCommunity Support
Hi Applicable88 ,
Could you please share the specific error message?
You can also see if this document is helpful: Promoting Headers Issue in Power Query
Best Regards,
Winniz