Column Name Changes Every Refresh

[Anonymous]

The excel dataset I am importing has the date and time for when the dataset was created in cell A1.  There are two blank rows below it, then in row 4 are all the column names, and then the actual data starts in row 5.  My users want the date in Cell A1 reported in my Power BI report.  

So far I have figured out to duplicate the column, replace all the data in rows 2-1000ish with a null, and then I "fill down" the column and end up with all the rows containing the time I want.  Unfortunately, I then have to Promote First Row as column names, and this is where my issue occurs.  The M code now has that date "hard coded" as the column name (4/20/2020 12:43:24 AM).  So when I went to refresh the data today the refresh threw and error because when a Changed Type applied step is run on this column, the new column header is 4/21/2020 12:33:23 AM however the olde hard coded date is still in my M code.

I am pulling the excel file from a file server and the "modified date" in the windows folder directory is not the same as the date and time I want to show in my report from cell A1. 

[v-xuding-msft]

Hi @Anonymous ,

Have you resolved the problem? If yes, please accept the helpful answer as solution. If you have any questions, please feel free to ask us.

[MFelix]

Hi @Anonymous ,

I had a similar question answered on this type of error on as previous post but don't remember the address basically you can do the following before promoting headers:

• Add an index column
• Add a calculated column with the following code:

if  [Index] = 1 then "Date" else [CustomColumn]

• If your index starts at 0 replace the 1 and use replace the "Date" by the name you need on the column the custom column is the one that were you have hard coded the date

Now delete the other custom column and the index column do the promote headers.

[v-xuding-msft]

Hi @Anonymous ,

Well, you have imported Excel file into Power BI and then the column name change to datetime (4/20/2020 12:43:24 AM). So the name is not same as it in Excel lead to refresh failed.  Do I understand correctly? If so, we need to rename it with the original name to make refresh successfully.

If I misunderstand,  can you please share a few screenshots?