Excel data changing format upon upload

[StefanoGrimaldi]

hey guys, 

having this case that I wonder if anyone knows why its doing it and how to avoid the query to do this: 

--> here you can see this line contains some date values formated as 3/1/2021 (march first 2021) 

--> but when a promote header its applied it chances the value to 1/3/2021 (in the dataflow query it does this even before promoted headers as quick as the data its upload it chances)

image from the sorce data: 

if anyone knows anything about this and how could be corrected please let me know, (better if the solution can be done on the query script )

[Anonymous · Accepted solution]

Looking at your code,  I see that your Excel Import includes Kind = Sheet.  If you have this in Table format in Excel, then you can change that code to Item = "Table Name", Kind = Table].  If your data is not in table format in excel, then you can turn it into a table by selecting the excel table and pressing CTRL+T.  If the info is in this current workbook, then Get data from Table/Range will do this automatically.  Honestly, though, I never considered whether or not coulmn names respect date formats or data types.

Wait though, can[t you make these columns type text, and then promote headers?  Once you do that, and then promote headers, you cna always go back and change the type.

--Nate

[StefanoGrimaldi]

I tried making them text no the excel file didnt work, set them as text column on the BI same it failed when promted the header xD, 

it did work the option you mention of converting it to a table on the excel,

thanks. 

the change type on dataflow option didnt work cause it was making the change on the import step, 

althought I solved this with your comment, still would be nice to find about this behaviour why it does it and how to control that on the import o.0 !!!!

[StefanoGrimaldi]

theres values on the first row and columns, just not showing in the croped images, like 5 columns with other information on the header that isnt dates, changing the data to type date, changes the values in the others rows, but on dataflows doesnt work the values comes changed even before promoting headers. its like its autoapplying a culture parameter when importing the file on the dataflow or promiting header on the power query desktop version, so would be a work around to avoid this upon data importing I guess?  

[Anonymous]

Have you tried to change the columns to type date before promoting headers? Also, the fact that you seem to have no value in the first row and first column of the source file might be a problem. Have you made this data a proper table object in Excel?

--Nate