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 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_dateapplication_updated_atapplication_created_at
43556.0043728.1943598.47

 

What's up with that?

Need Help

4 Replies

  • benjamin_sasin's avatar
    benjamin_sasin
    Resolver I
    6 years ago

    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:

     

  • 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.

  • benjamin_sasin's avatar
    benjamin_sasin
    Resolver I
    6 years ago

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