Help needed for a date format conversion

[maclura]

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:

Many errors, and many wrong date conversions.

The question is: which is the best way to convert this format in yyyy-mm-dd befor to import this table in Power BI?
Since I am definitely not good at M, the only solution I found is to convert it with DAX after importation.

Thanks for any hint.

[edhans · Accepted solution]

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.

[edhans]

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]

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

[edhans]

@maclura have you had a chance to review the proposed solutions?

[maclura]

Dear @edhans and @Anonymous 
thank you a lot for your most valuable advice.
Both of your suggested solution do work, but not for me.
I think there are other problems in that column originating from the cube.
I spent more than one day to investigate what and where the problem is without coming to an answer.

This is what happens.
When I preview that column I see

If I sort it in descending order I see

I remove the "Unknown" value and I appy your suggested conversion method (both of them do work)
And this is what I get

And if I sort this coulumn descending this is what I get

The only suggestion I've got from the guys administering that data source is to change the storage mode to DirectQuery, but this is an heavy compromise I cannot accept for my project.

On the other hand, using PowerQuery in Excel that Data column is correctly reccognized and you can play with the pivot table without any issue.

Thank you again for your advice.

[edhans]

The problem is @maclura , what is 9/9/30? That isn't a date format I am aware of. Is it Sept 30, 2009, Sept 9, 2030, or what? While the first to parts can be switched depending on location, the 3rd part is always year when there are slashes. So:

• MM/DD/YY
• DD/MM/YY
• never YY in the 1st or 2nd position in that format. It can be YYYY/MM/DD or YYYYMMDD or YYYY-MM-DD, but then it is always a 4 digit year and leaves nothing to the imagination.

I don't think it is that my solution or @Anonymous 's solution doesn't work. It is that your source data isn't really a date. You can use the following logic in a new column to split the data out and rearrange as necessary:

#date(
    Number.From(Text.AfterDelimiter([Column1], "/", 1)) + 2000,
    Number.From(Text.BetweenDelimiters([Column1], "/", "/", 0, 0)),
    Number.From(Text.BeforeDelimiter([Column1], "/"))
)

This could be done in place with out an extra column, but I'm not going to fool with tinkering the code at that level because honestly I don't know what the data your system is calling a date is really giving us.

[maclura]

Hi @edhans, there are issues in the data source (some source records have wrong data in it). This is causing all this mess.
Do you know if can I delete this tread, because what I tought was a problem at the beginning it's not anymore pertinent after my discoveries.

[edhans · Accepted solution]

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 · Accepted solution]

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]

@Anonymous you still have to convert the data type though, correct? The ABC/123 data type (any) will not be usable as a date in PowerBI. Can you do that with the TransformColumns step by adding the data type as the last parameter?

[Anonymous]

Thanks, Ed.

Precisely, but it is not clear will this be a date or text format from the tech description. Conversion is a reasonably simple step. My impression that it should be text. Technically, converting the output from the formula in my previous post to Date will most likely return an error as the yyyy-MM-dd format may not be suitable/clear for PBI.

Kind regards,

JB