Date Format

[GabrielK]

Hi, I have brought in an excel spread sheet as a data source. I have a date column, which is formated to short date in the excel sheet, but when i try change this column's type in the query editor from number to date, then all the dates in the column shows error. Please can someone assist. 

[GabrielK]

Hi Thanks for advise, unfortunately it did not work. Here is a snapshot of my problems. I have imported an excel spreadsheet to power BI, and power bi converts the durations into dates and I have tried chaning the type both in excel and power bi, but nothing works. 

Is there something im missing? 

[ronrsnfld]

You have several problems that I can see:

• Your table headers in Excel occupy two (2) rows, but you are only promoting a single row to be the headers, so your first row in PQ is text and return an error when you try to set non-text data types.
  • In Excel, (or you could do this in PQ), change your headers so they are all in a single row. You can include a linefeed character for readability if you wish
• In the Changed Type step, ensure you have the correct data types and culture setting.
  • You can edit this in either the formula bar in PQ, or the Advanced Editor
  • The Date columns, if they include the time, should be type datetime
    • If the show only a date, type date
  • The Duration columns should be type duration
  • The Climbing m column should be type number
  • The Culture argument should refer to some country that uses <space> as the thousands separator and <dot> as the decimal separator, since that is what you show in your data
    • Not knowing where this data comes from, "pt-PT" (portugal) is an example culture argument that seems to work, even though they use <comma> for the decimal.
    • The list of cultures that use <space> for thousands, <dot> for decimal, "/" for dates: 

Good luck.

[Anonymous]

Hi @GabrielK ,

Try using local:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Sahir_Maharaj]

Hello @GabrielK,

One approach is to create a new column for the date conversion:

ConvertedDate = 
VAR DateAsText = FORMAT([YourDateColumn], "0000-00-00")
RETURN
DATE(
    VALUE(MID(DateAsText, 1, 4)),
    VALUE(MID(DateAsText, 6, 2)),
    VALUE(MID(DateAsText, 9, 2))
)

Alternatively, if your date column is stored as a number:

ConvertedDate = 
DATE(1899, 12, 30) + [YourDateColumn]

Hope this helps - Alternatively, lets connect and I can guide you through the process - Yes, its FREE 🙂

 (https://topmate.io/sahirmaharaj/649292)