I found a simpler way, you can try it if it works for you. Right-click on the heading, go to change type, and click on 'using locale' at the bottom, select add a new step. 2 boxes to work on appear, first change the data type to date, then on the locale, select the country that has the date arrangement on your data. Often times, either English US, or English UK. I did this and it worked or me. You can also try it out. Or make a video of it as well.
I just found your channel today and sir you are absolutely doing a fantastic job. I am planning to make a switch into data analysis role and I had faced this date conversion error. Will surely try if these steps helps! Subscribed ❤
Hi, I am trying to this, but I am facing a problem. The column name shows an error. -- = Table.AddColumn(#"Filtered Rows", "IncidentdateNew", each try Date.FromText(Text.Start([incident date],2) & Text.Middle(incident [incident date],3,2) & "-" & Text.End([incident date],4)) otherwise Date.FromText(Text.Middle([incident date],3,2) & Text.Start([incident date],2) & "-" & Text.End([incident date],4))) Expression.Error: The name 'incident' wasn't recognized. Make sure it's spelled correctly. Could you please explain why this error??
When you use try and otherwise , and you get some rows in ddmmyyyy and other in MMddyyyy, it's not pointless? I mean to have dates in two different formats in the same column... can be quite confusing and lead to errors or mistakes
No I think you misunderstood. What we are doing here is checking if the original column had data in dd/mm or mm/dd format. Let me give you an example. If row1 has text 03/14/2024 and row2 has text 14/03/2024. The function that we implemented will first try to concatenate each part of the text in a dd/mm/yy format and then convert that to date using Date.FromText, if that gives an error then it concatenate text in a mm/dd/yy format and then use Date.FromText again. However the new column will all have data in one format.
Hello ! I am following your step but it still Error . It shows "We don't support the option 'format'. Could you please help how I can solve it. Thank you.
Hi, Try updating your power bi & check the spelling of the functions you are using in your Mquery. Also remember functions in Mquery are case sensitive.
One simple way that I can think is to create a duplicate of ur final table in power query which will serve as testing table. In this let's say u want to check the date column, then you can remove all other columns and apply remove duplicate. This will give u a small list of items. Then its easier to check if everything is good to go or not. Hope this helps!
@@pivotalstats thanks indeed for your suggestion. I have taken some subset of data in Excel to check but yes doing within powerbi should be a better option.
@@pivotalstats Even after applying the following steps the custom column is still showing error. I mean the inpatient and outpatient. I really want to practice this you Power BI visual
To fix this issue I just jump to power BI desk top and on data view section select the Archive_date column and click column tools at the top and under data type select date data type from the drop down & select the format too...🍰🥧🧁. your approach didn't work for me.
Great, thanks for sharing. The techniques shown in the video will have to be used only if the simple approach of changing data type does not work. Cheers!
I found a simpler way, you can try it if it works for you. Right-click on the heading, go to change type, and click on 'using locale' at the bottom, select add a new step. 2 boxes to work on appear, first change the data type to date, then on the locale, select the country that has the date arrangement on your data. Often times, either English US, or English UK. I did this and it worked or me. You can also try it out. Or make a video of it as well.
Oh great, thanks for sharing. I will definitely try it out. Cheers!
Finally something that worked for me!!! using country as ENG(UK)
Thanks for sharing !
Neither this or the format worked for me. Not sure what im doing wrong :/
It worked when I chose Hindi format in Locale. Thanks!
simple steps and perfect, many thanks
I just found your channel today and sir you are absolutely doing a fantastic job. I am planning to make a switch into data analysis role and I had faced this date conversion error. Will surely try if these steps helps! Subscribed ❤
Awesome, all the best :)
Nice explanation, the catch is while writing a formula in PQ/PBI never use dot in it. simply write the textend it will auto suggest
👍
Hi, I am trying to this, but I am facing a problem. The column name shows an error. --
= Table.AddColumn(#"Filtered Rows", "IncidentdateNew", each try
Date.FromText(Text.Start([incident date],2) & Text.Middle(incident [incident date],3,2) & "-" & Text.End([incident date],4))
otherwise
Date.FromText(Text.Middle([incident date],3,2) & Text.Start([incident date],2) & "-" & Text.End([incident date],4)))
Expression.Error: The name 'incident' wasn't recognized. Make sure it's spelled correctly.
Could you please explain why this error??
Excellent, you are sharing gold..thank you so much.
Glad it was helpful. Thanks for watching!
When you use try and otherwise , and you get some rows in ddmmyyyy and other in MMddyyyy, it's not pointless? I mean to have dates in two different formats in the same column... can be quite confusing and lead to errors or mistakes
No I think you misunderstood. What we are doing here is checking if the original column had data in dd/mm or mm/dd format. Let me give you an example. If row1 has text 03/14/2024 and row2 has text 14/03/2024. The function that we implemented will first try to concatenate each part of the text in a dd/mm/yy format and then convert that to date using Date.FromText, if that gives an error then it concatenate text in a mm/dd/yy format and then use Date.FromText again. However the new column will all have data in one format.
@@pivotalstats ah ok, yes I misunderstood. Thanks a lot!
Hi there! Thank you for your video, was really well explained.
Glad you enjoyed it! Thanks for watching :)
Hello ! I am following your step but it still Error . It shows "We don't support the option 'format'. Could you please help how I can solve it. Thank you.
Hi, Try updating your power bi & check the spelling of the functions you are using in your Mquery. Also remember functions in Mquery are case sensitive.
while working on large volume data - if we resolve date errors - how we can check if there is still no undesired outcome
One simple way that I can think is to create a duplicate of ur final table in power query which will serve as testing table. In this let's say u want to check the date column, then you can remove all other columns and apply remove duplicate. This will give u a small list of items. Then its easier to check if everything is good to go or not. Hope this helps!
@@pivotalstats thanks indeed for your suggestion.
I have taken some subset of data in Excel to check but yes doing within powerbi should be a better option.
please make videos from the various challenges like Maven, Onyx etc. so that we can start building dashboards on our own from that POV. 🎊
Thanks for a great idea! I have not looked into those challenges till now, but I will definitely plan something for future. Thanks!
Why you are not make the video daily i check your channel plz upload the vedio we are waiting
I recently moved to a new country, so I'm still settling down. I will try to be more consistent in coming months. Thanks so much for watching!
all of this doesn't work I don't know why !
Hi, Are you getting any error?
This video has not help with the issue of the error
In that case would you mind telling me the exact error you are getting. I might be able to help if I see the error message.
@@pivotalstats Even after applying the following steps the custom column is still showing error. I mean the inpatient and outpatient. I really want to practice this you Power BI visual
Helpful
👍
worked.huffffff. thanks
You're welcome! :)
To fix this issue I just jump to power BI desk top and on data view section select the Archive_date column and click column tools at the top and under data type select date data type from the drop down & select the format too...🍰🥧🧁. your approach didn't work for me.
Great, thanks for sharing. The techniques shown in the video will have to be used only if the simple approach of changing data type does not work. Cheers!
Thank you bro... Your solution worked..