Power Query date format incorrect

[571202]

Hi, when I refresh with new data the date format is incorrect, see below in yellow. I have to select the columns and manually format as 'Date' in the excel worksheet after I refresh. I originally created the power query in MS 2016 and now use it in MS 365, could that be the problem? Thanks in advance.

 

[Skovgaard]

Do you have a checkmark in "Preserve cell formatting" in the Data properties?

/Skovgaard

 

[571202]

Hi @Skovgaard Yes it is ticked. I have selected 'Date' in power query when I transformed the data and it all works fine in the first version I made in MS 2016......it only happens when I use it in Office 365, cheers.

 

[Skovgaard]

Hi @Skovgaard Yes it is ticked. I have selected 'Date' in power query when I transformed the data and it all works fine in the first version I made in MS 2016......it only happens when I use it in Office 365, cheers.

I think its the format in the cells, and not your query. Try to format all the date cells in your table so it looks correct, and then refresh your query, does it then still change the formats?

/Skovgaard

 

[571202]

Yes if I format the cells correctly to 'Date' and refresh they are fine, but when new data comes in and I refresh they are incorrect. I have selected the entire columns and formatted to 'Date' but when new data comes in they are incorrect.

 

[Skovgaard]

Hmm... Not sure...
Try and delete your table and then load it again to a new table, format the cells from the beginning and check if it still do so after refresh...

/Skovgaaard

 

[gelu]

Have you tried formatting the Date column "Using Locale" in Power Query?
Change Type > Using Locale > Date > English (US). Type F to get less scrolling for English US.

 

[571202]

Hi @Skovgaard I'll try that and see what happens when I get new data and refresh tomorrow, cheers

 

[571202]

Have you tried formatting the Date column "Using Locale" in Power Query?
Change Type > Using Locale > Date > English (US). Type F to get less scrolling for English US.

Yes I tried that. I did what Skovgaar suggested with some dummy data and it appears to have worked, I'll see what happens tomorrow with real data, cheers.

 

[571202]

Hmm... Not sure...
Try and delete your table and then load it again to a new table, format the cells from the beginning and check if it still do so after refresh...

/Skovgaaard

Hi @Skovgaard That worked perfectly, thanks heaps.