Using first row as a header issue

[naoyixue1]

Hey there, 

I tried to create the report to catch the latest 30 days excel file, which is extracted out of the other planning system. Givne that, individual file will have the name about when to extract. It causes the problem to rename the header after I use the first row as a header. Since the header is change (since the file name which is using as a header is keeping refresh), BI cannot handle that situation. (for example, on 9/15/23, my header will be 8/16/23; on 9/16/23, my header will be 8/17/23) (see below) 

So just check whether there is any work around, so BI can always rename the header from that file name to one specfic name such as file, etc. Any thoughts? 

[parry2k]

@naoyixue1 you will replace the previous step with the previous step name in your M: from your screenshot it looks like it is promoted headers1, so it will be:

 #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers1",{{Table.ColumnNames(#"Promoted Headers1"){0}, "New Column Name"}})

[naoyixue1 · Accepted solution]

Hey all, thanks. I just figured it out. In this case, I can use the create the conditional column to avoide that issue. Anyway, thank you though. 

[KEAlexander]

How about deleting the Source.name column first, then promoting headers?  Then if you need some part of the info in the Source.name column, create a new column and populate it with the needed information. 

[parry2k]

@naoyixue1 you can add a step to rename the column dynamically:

    #"Renamed Columns" = Table.RenameColumns(<Previous Step>,{{Table.ColumnNames(<Previous Step>){0}, "New Column Name"}})

[naoyixue1]

@parry2k  I'm guessing something is wrong about my M code, would you help to take a look into it? thanks!

[naoyixue1]

Hey there,

Just found it also causes the error when I try to uppivot the tabe, because that step is based on the header name. Not sure whether there is a workaround or I should totally do the other ways to pull the last 30 days file in one folder.  Anyway, thanks!