Power Query <> Excel confused about dates

[benjamin_sasin]

Hi, something weird is happening, where Excel doesn't seem to understand the dates from Power Query consistently. Here is a sample data that shows the discrepancy:

Somehow it understands the 3rd date but not the other two? The formatting of the date in the excel table is exactly the same. The date values are inherently different in excel even though they're the same in Power Query:

submitted_date	application_updated_at	application_created_at
43556.00	43728.19	43598.47

What's up with that?

[benjamin_sasin · Accepted solution]

This error was actually due to a bug in the source database. Apologies.

[v-juanli-msft]

Hi @benjamin_sasin 

First, please click on "refresh" on the top row.

The yop yellow line tells your data is old, the new data can't be automatically update to Power BI Desktop, please refresh by yourself.

As tested, it works on my side.

let
    Source = Excel.Workbook(File.Contents("**\***\****\Desktop\case\9\9.23\9.23.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"submitted_date", type datetime}, {"application_updated_at", type datetime}, {"application_created_at", type datetime}})
in
    #"Changed Type"

If my solution doesn't solve your problem, please share some code in Advanced editor or sample data for further analysis.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[benjamin_sasin]

Hi Maggie thank you.

Sorry I should probably have been more explicit: I'm importing data from a MySql database to Excel through PowerQuery.

So the dates come in as regular date-time data, up until they are loaded onto Excel. Then in Excel they appear erroneously.

In your example you're importing Excel dates to PowerBi, which does not replicate the error.

[benjamin_sasin]

PS: I just want to point at the fact this error isn't occuring in every row. Some display the correct date and time for some mysterious reason: