Extract Dates From Text Strings in Power Query

[@BIGorilla]

To learn more about the Date.FromText Syntax in M, check out: powerquery.how/date-fromtext/

[@fabmazzolini]

Thank you for the tip! Very useful when you start PowerBi with Power Query like me!!

[@ThePakdeeGaming]

I couldn’t have done it without you.

[@GeertDelmulle]

Your videos are always really good. You deserve ton of views and subs.

[@inetxem8000]

You are a crack! Without your tutorial I couldn't manage to treat the data the way I was looking for. Thank you so much!!

[@theozinvfx]

Thanks for this video. It was exactly what I was looking for.

[@VeganSmasher]

Thank you for this video. I was accidentally using "mm" instead of "MM". You rock!

[@mnowako]

Awesome! Each of your video is a great lesson. Thank you 👏

[@BIGorilla]

Thanks Marisuz, when I stumble upon these little tricks I really enjoy sharing them, glad you like it!

[@hamvasq]

Just what I needed, thanks!

[@malcolmbarrett9175]

Very Useful. My Bank csv files, the dates are half listed as text and the other half date format, even though they follow a sequential order. Video Helped immensely.

[@leebaldwin9198]

Thank you so much for this video. This solved my problem quickly, I only wish I had found it sooner. One tutorial I would appreciate is how to concatenate more than 2 fields in Power BI.

[@totvabe1]

Really, really useful!

[@BIGorilla]

Thanks!

[@gospelmoto2833]

Brilliant! Thanks, surely this is useful.

[@ezelkarlkl1284]

Hi , thank you for your all videos ! Could you give an example scarp data from web single URL and multiple tabs.

[@frjsk1247]

Awesome thanks a lot Rick!

[@smartwork4768]

Thank you

[@BIGorilla]

My pleasure, thanks!

[@vmax_bh1564]

Thanks for the lesson but I have a question. Does this formula works 100 on excel? I mean try and otherwise

[@jazzista1967]

H iRicky: Great video.... I am tryingto convert an old list of dates that ranges from the 1930s ( 12-SEP-39 , 21-Mar-40 , 09-OCT-13 etc ) to year 2013 and i don't know how to force the years from the 20th century in 19s... and the 21th century in 20's.. Do you have any suggestions? . Thanks

[@BIGorilla]

Jazzista. Nice to have you on the channel. A suggestion: perhaps you can transform the text values to include a year number. If last two characters are > 30 prefix with 19 else 20. And then do the conversion afterwards

[@jazzista1967]

Hi ricky.. i will try that... thanks for the quick reply . Take care!

[@einoconsult5563]

Great Video, thanks. However, hardcoding the formats could be challenging sometimes :) any chance to download the file ?

[@BIGorilla]

Hey Eino, No file at this point, but you can copy code from my written blogpost at: gorilla.bi/power-query/extract-date-from-text/

[@FRANKWHITE1996]

Great content

[@dineshpandey5008]

Hi, Thanks for this, That is Awesome but If i am not wrong It totally depend upon the System date&time format, other wise will through the error. In my case it is happening.

[@Manu-bn4lg]

Thak you !!

[@Faisal63045]

Hi is there a formula or PQ way to change any transactions time that come between 12:00:00 AM and 7:59:59 AM To be 8:00:00 AM which is the start of working hour.

[@J__Day]

Can this be done as transforming a column instead of adding a column?

[@christopherskyap8985]

Hi Sir, when I use Data.FromText([Column1], [Format = "ddMMyyyy"]). I got an error We cannot convert a value of type Record to type Text.

[@Micke01man]

Great video, My data is like "07/Sep/22 2:26 PM" (day/Month/Year Time) Tried with "DateTime.FromText", but dont understand how to put it when it comes to the AM/PM part. Any tip? Thanks

[@BIGorilla]

Hey Micke, You should aim for : DateTime.FromText( [DateColumn] ) It's a datetime format. This function extracts it. You can then transform this datetime to a date after if you like :)

[@Micke01man]

@@BIGorilla Thanks for quick response, but what I get back is "2007-09-22 14:26:00", so it doesnt understand that it should be 2022 and not 2007

[@BIGorilla]

@@Micke01man it all depends on your system's setting. Could you try: = DateTime.FromText( [DateColumn], [Culture = "nl-NL"] ) On my device it recognizes the value right away.

[@Micke01man]

@@BIGorilla Now it worked 🙂 Not sure why, my settings are Swedish, but when changing to "Culture = "en-US" it worked. Thanks for a great channel

[@BIGorilla]

@@Micke01man glad to hear that, thanks!

[@williamarthur4801]

Well interesting as always, but I really couldn't get anything other than default to work? I had a date as text 10/01/2020 and in Add column; Date.FromText([#"Date "] ,[Format = "dd MM yyyy" ] ) , I presume the #["Date"] is because it's a , I can't remember the term, but a word it uses . Any ideas?

[@BIGorilla]

For a date of 10/01/2020 you should be able to use: Date.FromText( [Date], [Format=“dd/MM/yyyy”] ) Does that help? As long as the column name has no space you don’t need #””

[@williamarthur4801]

@@BIGorilla No , still no good, I even tried copy / paste , as my column is called [Date] , i've made sure the original is formatted as text. re #" " , I've had this happed before, that is insists the formatting for a name with spaces, never been able to trace why.

[@BIGorilla]

@@williamarthur4801 , you can copy this code in the advanced editor to see a working example: let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLUNzDVNzIwMlSK1YlWMjbQNzADcY3AXAOgrCGCa2iqb2gE5cYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date Type", each Date.FromText( [Date], [ Format = "dd/MM/yyyy" ] ), type date ) in #"Added Custom"

[@williamarthur4801]

@@BIGorilla for that I just get unknown identifier, on a broader issue, is there any advantage to Date..FromText and just using Table.TransformColumnTypes(Source,{{"DATE",type date }}) , which appears on the code but changes to text first?

[@BIGorilla]

@@williamarthur4801 Date.toText helps when the regular transform columns doesn’t work. In the video I show several examples where the automatic date detection fails. In these cases you can specify the format 😁

[@hervelobe2497]

Hi Rick, hope you can help out here. I had a challenge at a job interview process where I was given a dataset with the date column in year & quarter and the column format as Time. I was unable to transform it into a proper Year-quarter. Part od the dataset below. Time 201501 201504 201507 201510 Thank you

[@helmutwasser5441]

Hi Bi Gorilla, thank you for your video but in my case, it did not help me. Could you help me with this issue. I have a data import from hundreds of CVS files, and they all look the same. The date does not come as a real date. I am getting for example in the Date Row 45371.49955 and I cannot get that converted to a Date. I checked that in excel and easily got a DateTime --> 20.03.2024 11:59:21

[@raitup00]

A data model was created in Portuguese and I am struggling a lot with the language settings. On the microsoft website it says: "Once created the language of a Power BI model can't be changed". The only option is modeling again. It's a pain in the neck

[@rebecanewton5144]

What if my date comes only as a month named 'M1, M2, M3' and so on?

[@ericaleverson9430]

Using otherwise did not work in my power query because it did not exist. There were red lines under otherwise. I am in the US

[@BIGorilla]

I'm not sure Erica. As far as I can tell the syntax is the same in every location. What does your formula look like?

[@ericaleverson9430]

@@BIGorilla The syntax you used with the otherwise and try did not show up as a syntax in the power query. You can also tell because it does not show in the tellisense

[@BIGorilla]

@@ericaleverson9430 Intellisense in M only suggests items like enumerations, types and functions. Operators and control structures, like try..otherwise are not suggested. Without seeing your espression it’s hard to tell.

[@ymn76ymn]

👍

[@BIGorilla]

Thanks