Change of sample file for folder load got error/ promote headers alternative

[Applicable88]

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. 

[serpiva64 · Accepted solution]

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 !

[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. 

[Applicable88]

@serpiva64 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.

[v-kkf-msft]

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