Thank you, Jon. Before I retired, I worked for a Japanese company that used the ISO date format yyyy-mm-dd, and after a lot of frustration, I stumbled on the operating system fix. I didn't know about Power Query then, nor your excellent solutions.
Can't quite believe it , I encountered this problem this morning and didn't know how to fix it without changing windows regional settings. I wasn't even looking for a solution, just selected by chance. As with all your videos you have a very clear and relaxed way of explaining things.
Thank you. Regarding creating extra column by example, what if you have the dates spread across two or more columns? As you probably know, real life problems do not come as easy as having the dates all put together under one column. 100% of the time you will have them rather spread across multiple columns and where some of these relevant columns containing dates do also contain other important stuff - that's the real life challenge that makes power query somewhat useless. Since cleaning is where the real challenge is, we'd all appreciate if you could tackle in more videos more cleaning challenges. Imagine you have daily report workbooks each containing just one sheet. Although all sheets follow similar pattern (i.e. name, surname, employee ID, role etc.), yet the columns containing a specific data (i.e. name) often get replaced by a column or two (because of converting .pdf into .xlsx). In other words, while name was reported under column C in one report, it gets reported in column D in the other report. And in the third report it gets reported in column E! That's the real challenge.
Thank you for this tutorial. However, I have encountered a different scenario today, my dataset has two different date types E.g 7/01/21 & 01/7/21. How to can I fix this, Thank you
I've used one or two of these methods before, with success. Today I downloaded 'balance affecting activity' as a CSV from Paypal Canada and none of the 4 methods you show are working. I'm in Canada and am used to my excel getting confused about the day/mo/yr vs mo/day/yr, but this is very odd.
It is very informative but i have an issue, I have imported a file and my date column is m/d/y format and i want to change it in d/m/y format but problem is all dates which are not as text format already converted as date suppose, 1st feb converted to 2nd jan autometically, which is wrong
I have a question When I import data from system Date/time has been stamp by user but some date not stamp Date/time and effect to error How to replace in this error? Thank you
Thank you for your great advise. Changing locale for the file worked for me. Before watching your video, I was going in bizarre loops trying to fix multiple date formats in the same column. changing file locale then use change date function inside power BI did the job. Thank you again!
Thanks for the great video! That clears up how to IMPORT dates from data sources that have different locals. Does anyone here know how to then SHOW values in a specfic way, regardless of end users' locales? (specifically numeric values). In Tableau it's so easy to force the display format, by selecting which symbol to use for thousands separator, and which symbol to use for decimal seperator. Seems impossible in Power BI.
Hi, Very useful video thanks. One very common issue bt when column add or delete in source csv file.how can we resolve it. Bc csv files keep updating with new releases.
Us mathematicians insist that yyyy/mm/dd is the only reasonable way to do it. It's the only format that sorts OK as text, as numbers (without the punctuation), or as dates. I always use this approach in file names, where it's the only way to get sorts right.
John, thanks for the video. It is very clear and usefull. In the las example, maybe you could also substitute the "--" for the "." and then convert to date.
Well explained .... I have a question... how did u get the option "Add column" in the Ribbon bar?? ... I am using excel from Office 365 latest version but I tried to add this option and it doesn't exist ... can u give me a hint??
Thank you, Jon. Before I retired, I worked for a Japanese company that used the ISO date format yyyy-mm-dd, and after a lot of frustration, I stumbled on the operating system fix. I didn't know about Power Query then, nor your excellent solutions.
Can't quite believe it , I encountered this problem this morning and didn't know how to fix it without changing windows regional settings. I wasn't even looking for a solution, just selected by chance.
As with all your videos you have a very clear and relaxed way of explaining things.
Love You man,
From India..
😀 Thank you so much... !!
Awesome explanation ☺️
Thank you very much!
You're welcome, Archie! :)
using local for a column worked .. thanks
Glad it helped 😀
Thank you for this, really helped me out
Glad it helped! 😀
Very useful and solved my issues
Great to hear that. 😀
million thanks
Thank you for this tips!!! I liked a lot, see you later.
Thank you. Regarding creating extra column by example, what if you have the dates spread across two or more columns? As you probably know, real life problems do not come as easy as having the dates all put together under one column. 100% of the time you will have them rather spread across multiple columns and where some of these relevant columns containing dates do also contain other important stuff - that's the real life challenge that makes power query somewhat useless. Since cleaning is where the real challenge is, we'd all appreciate if you could tackle in more videos more cleaning challenges. Imagine you have daily report workbooks each containing just one sheet. Although all sheets follow similar pattern (i.e. name, surname, employee ID, role etc.), yet the columns containing a specific data (i.e. name) often get replaced by a column or two (because of converting .pdf into .xlsx). In other words, while name was reported under column C in one report, it gets reported in column D in the other report. And in the third report it gets reported in column E! That's the real challenge.
You can try to add condition column
Thank You for sharing this very IMP info Vid.
You're welcome! 😀
As always, great! thank you
Great , Many help thx , keep uploading more video on Query with Attached file also, help to use practices thx
Will do, thanks! 🙂
Thank you for this tutorial. However, I have encountered a different scenario today, my dataset has two different date types E.g 7/01/21 & 01/7/21. How to can I fix this, Thank you
OMFG after 3 fking hours I finally was able to reformat the dates. omgggggggggggggggggg TYYYYYYYYYYYYYYYYYYYYYYYYYYYY
Thankyou, custom locale was the best tip. American dates smh
Excellent Really Excellent...
It really helped me! thanks
Thank you for the help..
Thank you very much
Love this video 🙂
Still getting error for dates before a particular year say 1954. How to address that?
I did that and the format is ok in power query but when I load it in the table it just appears as numbers
instead of dates. Anyone has any solution?
A lot of thanks
Thank you
Thanks a lot bruv. Before looking at your video, I tried many other solution. Your solution worked like a charm..
Thank you, 1 day spend for change date to month name in locale. watch your video at 02:58, wola, its finished.
Thank you for sharing the useful information............................
Thanks for the video. Applied the first step and problem was solved
Amazing ... i already was having such a problem ...thanks a lot
Thank you very much for the tutorial. The 4th part solved my problem, thanks again!
Very well Sir John, thanks to my increase knowledge...
I've used one or two of these methods before, with success. Today I downloaded 'balance affecting activity' as a CSV from Paypal Canada and none of the 4 methods you show are working. I'm in Canada and am used to my excel getting confused about the day/mo/yr vs mo/day/yr, but this is very odd.
Great Tutorial..Thank You Jon :-)
Thanks Darryl! 🙌
Excellent demonstration Jon, always enjoy your videos...
It is very informative but i have an issue, I have imported a file and my date column is m/d/y format and i want to change it in d/m/y format but problem is all dates which are not as text format already converted as date suppose, 1st feb converted to 2nd jan autometically, which is wrong
I have a question
When I import data from system Date/time has been stamp by user but some date not stamp Date/time and effect to error
How to replace in this error?
Thank you
Awsome Tutorial. Exactly to the point. Thanks a lot.
Thank you so much 👍
You are welcome! 😀
Thanks a lot man.
This will help me a lot
Amazing As Usual :)
Thanks, Maytham! 😀
Hi Jon.. great tutorial.. demystifies the issue of date formats across regions and how to handle them. Thanks for sharing. Thumbs up!
Thanks a lot. This is so helpful and it saves my day.
Thank u so much i follow step 1 and then 2 and its work for me... A big Thank u Allah Bless you.
You're welcome! 😊
thank you Jon.!
You're welcome, @Vishnuputhiyedam92! 😀
Yes it very helpful tips
Thank you so much. That was very helpful!
Thank you Messeletch! I'm happy to hear it helped. 🙂
Half of my dates are mm/dd/yyy and half are dd/mm/yyyy in one column. What to do now?
excellent explanation, thank you Jon
THANK YOU VERY MUCH, SUCH AMEANINGFUL VIDEO 🙌
Glad you liked it! 😀
Brilliant tutorial. Thanks
Thank u so much sir
You are a life saver
Thank you for your great advise. Changing locale for the file worked for me. Before watching your video, I was going in bizarre loops trying to fix multiple date formats in the same column. changing file locale then use change date function inside power BI did the job. Thank you again!
Excellent!
4 th step not working
Thank you so much Jon! Brilliant!
You're welcome! 😀
Hi Sir, i followed the steps but it still did not work :(...But Thanks a lot. May be issue with the file. I will create a new file and try. :)
Your explanation is excellent 👌 thank you so much, it easy to follow and understand❤
Happy to hear that! 😀
Thanks for the great video! That clears up how to IMPORT dates from data sources that have different locals. Does anyone here know how to then SHOW values in a specfic way, regardless of end users' locales? (specifically numeric values). In Tableau it's so easy to force the display format, by selecting which symbol to use for thousands separator, and which symbol to use for decimal seperator. Seems impossible in Power BI.
Hi,
Very useful video thanks.
One very common issue bt when column add or delete in source csv file.how can we resolve it. Bc csv files keep updating with new releases.
Thank you, Jon. highly informative video.
You're welcome! 😀
Very well done Jon - thanks
Thanks Andover! 🙂
Us mathematicians insist that yyyy/mm/dd is the only reasonable way to do it. It's the only format that sorts OK as text, as numbers (without the punctuation), or as dates. I always use this approach in file names, where it's the only way to get sorts right.
Good video
Thank you very much, I was solving tis prob for awhile and then I found your video which helped me solved this prob
You're welcome, Gingerx! 😀
Jon, in the blog, you could download the file to practice. But it is missing the csv files. Thanks
Thank you so much it was very useful
Glad it helped, @sharonnwanne5098! 😀
John, thanks for the video. It is very clear and usefull. In the las example, maybe you could also substitute the "--" for the "." and then convert to date.
It worked.. thanks a lot
Glad it helped 😀
Well explained .... I have a question... how did u get the option "Add column" in the Ribbon bar?? ... I am using excel from Office 365 latest version but I tried to add this option and it doesn't exist ... can u give me a hint??
This command is not in Excel, you have to be in PQ add-in to see thus command.
Very useful!
Thanks be ze! 🙂
Thank you for this. You're a life saver!
Glad it helped! 😀