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
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
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
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
@@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
@@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.
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.
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.
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.
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
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.
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
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 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.
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?
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 #””
@@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.
@@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"
@@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?
@@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 😁
To learn more about the Date.FromText Syntax in M, check out: powerquery.how/date-fromtext/
Thank you for this video. I was accidentally using "mm" instead of "MM". You rock!
Awesome! Each of your video is a great lesson. Thank you 👏
Thanks Marisuz, when I stumble upon these little tricks I really enjoy sharing them, glad you like it!
Thanks for this video. It was exactly what I was looking for.
Brilliant! Thanks, surely this is useful.
Thak you !!
Thanks for the lesson but I have a question. Does this formula works 100 on excel? I mean try and otherwise
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
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
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.
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
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
Hi ricky.. i will try that... thanks for the quick reply . Take care!
Great Video, thanks. However, hardcoding the formats could be challenging sometimes :) any chance to download the file ?
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/
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
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?
@@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
@@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.
Thank you
My pleasure, thanks!
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.
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.
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.
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
Really, really useful!
Thanks!
Hi , thank you for your all videos ! Could you give an example scarp data from web single URL and multiple tabs.
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.
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!!
Can this be done as transforming a column instead of adding a column?
What if my date comes only as a month named 'M1, M2, M3' and so on?
Your videos are always really good. You deserve ton of views and subs.
👍
Thanks
I couldn’t have done it without you.
Awesome thanks a lot Rick!
Just what I needed, thanks!
Great content
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
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 :)
@@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
@@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.
@@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
@@Micke01man glad to hear that, thanks!
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?
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 #””
@@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.
@@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"
@@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?
@@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 😁