Forum Discussion

benjamin_sasin's avatar
benjamin_sasin
Resolver I
6 years ago
Solved

Power Query <> Excel confused about dates

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...
Need Help
  • benjamin_sasin's avatar
    benjamin_sasin
    6 years ago

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

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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's avatar
benjamin_sasin
Resolver I
6 years ago

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.