Forum Discussion

maclura's avatar
maclura
Resolver I
5 years ago
Solved

Help needed for a date format conversion

Hi, I have this table coming from a SQL cube which offers to me a Time.Date column formatted in this way Of course, if I try to change its Text type in Date (or Date/Time), this is the result...
Need Help
Tips and Tricks
  • edhans's avatar
    edhans
    5 years ago

    Use the "with Locale" setting. Click on the ABC data type in the upper left of the column, then select "using locale" at the very bottom of the list, then set it like below:

    I just use Bahamas because I know it uses DD/MM/YY as the format and will work. Use whatever locale your data is coming from. It returns this:

     

     

    If you need further help, please post data we can use in a table format per links below.

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi maclura,

     

    alternatively, you can use Table.TransformColumns instead of Table.TransformColumnTypes. This is particularly helpful when dealing with multiple date formats in a single dataset:

     

    Table.TransformColumns(Source,{{"Column1", (x) => Date.ToText(Date.From(x, "en-GB"), "yyyy-MM-dd")}})

     

    Replace the formula in your type transforming step in the Advanced editor to the above and replace Source in the formula to the name of the preceding step in your query. 

     

    Kind regards,

    JB

     

edhans's avatar
edhans
Community Champion
5 years ago

Don't think so. You could mark mine and/or Anonymous as the solution because they are a solution for the data presented. However, as you've discovered, the devil is in the details and the deeper you get in a project, the worse the mess can get. ðŸ˜‚

That way others will learn, and this thread is marked as solved.

  • maclura's avatar
    maclura
    Resolver I
    5 years ago

    Hi edhans and Anonymous I will mark your answers as solution because you deserve it for all the free support you provided to me. At the end you helped me to understand where the problem resides.

    And the problem is that with this cube you can't choice "Import" as a storage mode, only "DirectQuery".

    When you connect to it with DirectQuery you don't need to convert any date format at all, they just render according to your locale settings.

    Thank you again for your help