Power Query Date

Question

Hi

I am using PowerQuery to get data from a website. The date format isn't the format I need. It uses the format "MM/DD/YY" but I need "YYYY-MM-DD". I have written an Excel function that does the conversion. I do not know how to translate that Excel function to one that Power Query understands. Is someone able to help with the translation?

Here is the Excel function:

=DATE(IF(VALUE(RIGHT(A2,2))>=90,1900+VALUE(RIGHT(A2,2)),2000+VALUE(RIGHT(A2,2))),LEFT(A2,2),MID(A2,4,2))

Thank you so much.

Answer 1

Use Date.ToText in PQ to convert the date to your text format (if that is really necessary).

Andreas.

Answer 2

I tried that function but it didn't work. The problem is that the date is a string and not a date.

In this case you have imported the data wrong.

At first read this article how to change the locale in PQ to import data that does not match your settings. If the source is a CSV file or a Website doesn't really matter:

Power Query - How to import a CSV file that does not match your locale - Microsoft Community

If that doesn't help: Did you tried to right-click your date column and choose "Change type \ Using Locale" and choose English (USA) to convert the text into a real date.

Andreas.

Answer 3

Import the date field from the website.

In PQ, make sure to convert the date data to Date Data Type. DON'T worry about the display format. Just make sure it is treated as a date. After you close and save to Excel you can then apply the desired custom date format.

.

If you are lucky, the text format will simply allow you to apply date datatype to the whole column with no problem. Otherwise you will have to do text conversion magic to create the date datatype. Exactly what you have to do depends on your incoming data.

.

Here are some articles on how to convert text to date in PQ

https://www.youtube.com/watch?v=0RN3FZv3w84 https://docs.microsoft.com/en-us/powerquery-m/date-fromtext

https://blog.enterprisedna.co/change-date-formats-using-the-power-query-editor/

Answer 4

I tried that function but it didn't work. The problem is that the date is a string and not a date. I tried below but I am getting error. How do I fix it so it works?

The below concatenates year, month, and day into the format I am looking for.

day -> Text.Middle([Date],3,2)

mnth -> Text.Start([Date],2)
year -> if (Value.FromText(Text.End([Date],2)) >= 90) then (Value.FromText(Text.End([Date],2)) + 1900) else (Value.FromText(Text.End([Date],2)) + 2000)

all combined:

Text.Combine({if (Value.FromText(Text.End([Date],2)) >= 90) then (Value.FromText(Text.End([Date],2)) + 1900) else (Value.FromText(Text.End([Date],2)) + 2000),Text.Start([Date],2),Text.Middle([Date],3,2)},"/")