Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
drew_darke
Frequent Visitor

How to transform a date thats yy-mm-dd into yyyy-mm-dd?

The issue that occurs is when the date format in the dataset is in a 6 char date format, example: July 4th, 2020 is noted as 20-07-04.

This throws errors when trying to straight up convert the column to a date type. And when creating a custom column to format the date to yyyy-mm-dd we get the following problem:

July 4th, 2020 (noted as 20-07-04 in the data) will return 2020-07-04, great.

However, July 4th, 1996 (noted as 96-07-04 in the data) will return 2096-07-04 in the new custom column.


For the more visual people, here is a screenshot

drew_darke_0-1628609961714.png

Obviously the AI is just finding the pattern of "Oh looks like I'll just concat 20 to beginning of the year" but this doesnt work.

(And unfortunetely starting the custom column on the fields with 19XX does not fix the problem, it actually just appends 19 to everything instead)

Anybody got a trick to work around this?

 

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @drew_darke ,

 

Try add a custom column:

 

=#date(
    Number.FromText(
        if Text.Start([date],1) >= "3" 
          then "19" & Text.Start([date],2) 
          else "20" & Text.Start([date],2)
    ),
    Number.FromText(Text.Range([date],3,2)),
    Number.FromText(Text.End([date],2))
)

image.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
v-kkf-msft
Community Support
Community Support

Hi @drew_darke ,

 

Try add a custom column:

 

=#date(
    Number.FromText(
        if Text.Start([date],1) >= "3" 
          then "19" & Text.Start([date],2) 
          else "20" & Text.Start([date],2)
    ),
    Number.FromText(Text.Range([date],3,2)),
    Number.FromText(Text.End([date],2))
)

image.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

CNENFRNL
Community Champion
Community Champion

PQ is intelligent enough beyond expectation,

Screenshot 2021-08-10 203448.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.