Brilliant lesson, Mynda, as always. Clear. Crisp. No waffling around (love your phrase). I am often faced with global database dates in... text format. Your lesson is cool, clever, and instantly actionable for me. I always learn more than I ever expect. Well done.
Thank you for the extremely useful video. When you did the change date to locale at 12:00, you set it as the US. but your Excel default is Australian. So if your default was something else, (say another country that uses American style, It would just load it as American style. Is that correct?
That's because Excel thinks the dates in the CSV file are d/m/y format, whereas when it's opened with Power Query you can correctly convert the dates from m/d/y to d/m/y. You can test it yourself by downloading the files via the link in the video description.
Hi Mynda, quick question, your explanation is fine if you have 1 column of date formatting issues but i have multiple date columns per tab and 6 tabs. Splitting each column across 6 tabs would take me ages !! How do i re-apply the fix across multiple columns/tabs/sheets?
Hi Daniel, it really depends on how the date is formatted, but either way you'd have to apply the fix to each tab as a separate query unless you're consolidating the data into one table. It's difficult to help here, but if you post your question and sample Excel file on our forum someone can help you further: www.myonlinetraininghub.com/excel-forum
at 7:28 example four I have this exact problem but mine will not convert like yours. I have tried every scenario and it still does not convert to a numeric value remains as text.
I work with us date format. This is super easy formatting tips. Generally i use text function to change date day and month but i have to do it every time from us to india or india to us format. Thanks a lot mam
Thanks Mynda that's very helpful. I have file coming to me from Project Managers all over the world and so from mutiple locales. They are all text fields. Is there a way to process data from multiple locales into the format in my locale. Concretely, I have text fields from UK in 01122020 format and12012020 format. Thanks
Great to hear it'll be helpful, Seamus! For the different locale files you'd need to change the type using locale on each individual file before appending them into one consolidated table.
Thanks for the lesson - learned a lot. I have a Question about the Import. My csv Data shows the Date like 02.01.2021 (EU 02.Jan, 2021) when i load in my files to Power Query it shows me a text 2012021 - other Dates like 30.01.2021 will show up as 30012021 (1 more Number) With both i have wrong data with Dates in future or an "Error" is showing up - is there any fix to that Problem? - or is there a way to tell power query that he just have to import the data without removing something like the Dots or the Zerro in front? Thanks a lot
Hi Christian, You can use Text.PadStart to add a zero to the dates 1st to 9th of each month. Then you can split the result into the 3 date components (day, month and year) and rebuild the date using the #date function. If you get stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I have a question. What happens when you have a row of promoted date headers that need to be changed from text? Right now, I just convert them in excel to become the right data type so that I can use Index/Match/Match to make a report. Wondering how that one row gets converted into the right date format so I can avoid the extra step in Excel.
Hello, Mynda. Just wondering what would be the solution to dates formatted as 2/6/2018, 6/2/2018, 06/02/2018, 02/06/2018, 6/2/2018, 2/6/2018, 6/02/2018 into one date format as there have been different format texts from a file in the past I'm keen to convert into DD-MM-YYYY? Length of texts is 6,7,8,10 digits if I replace /. For example: Some of them formatted as the month in very left side while other months are positioned in the middle.
Hi Bilguun, you can't do this in Power Query because all dates in the column aren't the same format i.e. either text or number. Instead, in Excel add a column that determines whether the dates are text or not using the TEXT function. Then sort the column based on the result. You can then isolate the text values and fix them using one of the options suggested here: www.myonlinetraininghub.com/fixing-excel-dates-formatted-text
@@MyOnlineTrainingHub Thanks, Mynda. What I did was first I separated them using text to columns, then using a formula that will merge them together for example, if F,G,H columns contains either MM,DD,YYYY or DD,MM,YYYY, used =F2&"/"&G2&"/"&H2 formula to join them, then using =date value, lastly format cells so I can convert them to my desired format which is DD/MM/YYYY, but came back as multiple errors as a few of them formatted in 10 digits like 1/2/2020 which could be 01/Feb/2020 or 02/Jan/2020. Pretty long process, isn't it? If I could use just one formula which will isolate them while putting them to only one format would be fantastic... Not sure if this could be possible but what about Using IFERROR, IF formulas to put these bad guys into one column or even with VLOOKUP just for the sake of it would do the magic and if anyone knows how to, your assistance on this matter will be invaluable. Cheers
Mynda, I've come across dates where its shown as August 1st or July 2nd etc. I've been using Replace to remove st, nd, rd, and th from the dates and then a simple date format change is done, but I'm wondering if you have a simpler way of formatting this?
@@MyOnlineTrainingHub I should have said my dates show as January 4th, 2021. At the moment I find if I remove the nd/rd/th with replace it works fine but with "st" because of August ending with "st" I need to use a replace of "st," to save the spelling of August. Any other suggestions?
I want to learn more about the dax formula that you've done in dashboard post. Could you please send me a relevant course, please? I might learn it over the Christmas break.😁
Hello, I have a data source where the date is written as 1st, 2nd, 3rd... of each month and year. The Date.FromText function returns an error. Is there an easy way to convert a string such as '8th November 2023' to a date? I often watch your Excel videos and find them very informative, so thank you for the very useful guides which you produce.
Hi Nigel, you could split the number from the 'nd' 'rd' etc. text or replace these text strings with nothing to isolate the number, then it should convert to a date more easily. If you're stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub .. Thank you.. yes, I did a bit more investigating and splitting the text into constituent parts does seem to be the only method, then applying several steps to remove the ordinal suffixes, then recombine into a date string which Power Query can handle. It does seem like a curious oversight.
Hi Mamadou, thanks for your interest in my courses. My Udemy courses are out of date. I'm not using that site anymore, but you can get all my courses from my own site here: www.myonlinetraininghub.com/
Hi Mynda, great video! I have a question: how can I calculate the time (duration in hh:mm:ss) in Power Query / Power BI? For example, I need to add working hours for different projects. In my Excel file, I have the duration for each activity. When I work with my table in Power Query or Power BI, the format change into a date-time format and I can't do calculations with that.
@@MyOnlineTrainingHub Madam yes madam, it directs me to that link and asking to enter my email address even after providing email, I am getting the download. please provide the practice file. please madam.
As a Canadian who's lived in the UK and US, I've run into this issue for decades, esp the last 2, after incorporating one of our cos in the US. So I've got bank records with both, often intermingling in the same database (with a USD to CAD conversion column), and have used a variety of workarounds. Last year I watched your wonderful channel and starting using Power Query. Thanks for that! However, today I went to transform some dates listed 'dd/mm/yyyy' by using the LOCALE method, and after selecting DATE I chose 'English (Canada)'. The result was not what I expected (as here in Canada we've grown up with day month year): all of those dates in the 'mm' position were deleted (I forget what PQ called them) leaving me with only the dates that had month values at 12 or less. Working on a theory that Canada may have finally been subsumed into USA accounting formats I tried selecting 'English (Australia)' instead: AND IT WORKED! We need some sort of international agreement: For the last two decades I've been converting ALL dates to DD-MMM-YYYY [if that's how you get 21-MAY-2022] > I realize it's just so we can deal with the yanks, but what else to do?
I found this lesson extremely helpful. However, I have 2 quick questions: in the custom formating option, why the month and year have more letters than day like this: d/mm/yyyy. My 2nd question is that I notice there is a date/time/timezone option in the power query but don't know why you don't select it instead.
Glad you found my video helpful, Toan :-) To answer your questions: 1. d/mm/yyyy will format dates with a single digit day with one character e.g. 1/10/2020 whereas dd/mm/yyyy will format it 01/10/2020 2. Date/Time/Timezone format requires the timezone to be part of the value e.g. how many hours from UTC the time is. More on date timezones in Power Query here: docs.microsoft.com/en-us/powerquery-m/datetimezone-from
Stuck on the first example ma'am. It keeps returning "Error" for the merged column after trying to change the data type from number to date. I'm confused.
I download csv file from my bank and this one bank's date always causes an issue which after watching your clear and concise video, still has yet to solve the issue. I finally just typed out the dates to get it to work (time consuming from all angles). I would love the silver bullet on these dates.
This was so beautiful because I was stuck in trying to change January to Jan and February to Feb but when you said it is done in excel and not in Power Query. I thought OMG now I know why I got stuck. Didn't know that you can't change full month names to shorter names in PQ. Why did you put 0,2,4 for Positions? This is confusing. In my example, some are 11/29/2019 and some are 1/7/2018 so what would be positioned for these examples? Thanks
Glad you found it useful! The 0,2,4 example only applies to dates in this format: 18072020 or 01122020 etc. as in the example shown i.e. where there are no delimiters and the structure is ddmmyyyy or mmddyyyy. If you have delimiters, like the forward slash, then you would use one of the other techniques I showed.
@@MyOnlineTrainingHub You know what? I was able to get shorter months from Power Query. Someone told me that I will need to manipulate M Code and that is what I did and I was able to get Shorter months from PowerQuery. Thought of letting you know that you can do it in PowerQuery. Thanks
Yes, you can, but those months are now text and therefore not usable in formulas, nor will they sort properly because Excel cannot understand them as calendar periods. This is why formatting like this is best done in Excel using cell formatting on date values.
Hi Lynda, What I want to learn is that since there are Microsoft engineers who developed the DAX software language and have a much broader formula and data analysis power with the use of DAX, why do the formulas we use in excel have such limited capacity? More precisely, when there was a powerful analysis program like Excel, why was the need to develop a program like PowerQuery and use a different software language? Thank you
Hi Emre! DAX is designed to be used on relational data, whereas Excel functions are not. This new capacity with Power Pivot required a new language that's design is modeled on the familiar Excel functions. Keep in mind that Excel is built on millions of lines of 35+ year old code. It's not so easy to just change, but we're seeing massive improvements to Excel functions e.g. cells containing rich data types, dynamic arrays, LET and LAMBDA to name a few and there's more coming. Power query is a different tool, it's all about getting and transforming data and that requires a more advanced coding language than Excel functions can give us. The Excel team did a great job of automating the writing of this code by giving us a nice GUI that allows us to click some buttons and have the code written for us.
Thank you so much Mynda! I've been struggling with the last form of date conversion every month for years now and never thought to try PQ... it works flawlessly!
Hi Kiran, this is a format which is applied in the Excel worksheet after 'closing & loading' the query. e.g. dd mmm yy More on custom number formatting here: www.myonlinetraininghub.com/excel-custom-number-format-guide
Mynda I'm brazilian and want to enormously THANK YOU for the lessons. Literally saved my job! God bless your job.
Wow, so pleased I could help!
@@MyOnlineTrainingHub 🙏🙏🙏🙏
Brilliant lesson, Mynda, as always. Clear. Crisp. No waffling around (love your phrase). I am often faced with global database dates in... text format. Your lesson is cool, clever, and instantly actionable for me. I always learn more than I ever expect. Well done.
Thanks for your kind words, Libby! So pleased you can make use of it :-)
Honestly great teaching methodology. Facing dates issues from SAP Data in excel. Great 👍
I cannot believe the timeliness of this video! I had this exact problem this week. Great help. Thank you for the video instruction. So simple!
Great to hear, George!
Beautiful exposition! Also wanted to mention that I took your dashboard course and I use the techniques I learned there every day.
Awesome to hear!
Thanks Mynda! dates are one of the most important data in data bases, these conversions are really usefull!!
Glad it was helpful, Felipe!
Where has this been all week? I have been stuck trying to solve the issue with mixed date. You definately have a new sub. Thanks sooooooo much
Awesome to hear, Mike!
Mynda, thank you so much for saving me from trying everything that did not work.
Brilliant video as usual.
Glad I could help!
Thank you for the extremely useful video. When you did the change date to locale at 12:00, you set it as the US. but your Excel default is Australian. So if your default was something else, (say another country that uses American style, It would just load it as American style. Is that correct?
No. The locale you choose in the dialog box is the locale of the source data. Power Query will then convert it to the locale that your PC is set to.
I follow all your videos with great expectation, especially those about Power Query. Thank you very much for your dedication.
Glad you like them, Ivan!
A talented expert I have ever seen before. A lots of respect from my heart ♥. God bless you.
Thanks for your kind words :-)
Thank you so much for this wonderful lesson. I wonder if I can use the power query with the Mac version of Excel?
Hi Mynda. Awesome examples! Thanks for showing the various ways to use Power Query to fix dates formatted as text. Thumbs up!!
Thanks, Wayne!
Wow, Mynda, once again you read my mind and I got this video just in time, when I was struggling with dates in CSV! Thank you so much! 😊👍
:-D glad I could help, Vijay!
Wow. Learnt two new things today: splitting by position and column from examples. How did I not know it was that easy? Thank you!!
Great to hear, Lindsey!
Was there some step skip at 11:34? As data opened from CSV seems different than what was left in Excel.
That's because Excel thinks the dates in the CSV file are d/m/y format, whereas when it's opened with Power Query you can correctly convert the dates from m/d/y to d/m/y. You can test it yourself by downloading the files via the link in the video description.
thanks alot, spent hours searching for a fix, you are brilliant. from SG
You're welcome! Glad it helped!
Many thanks I had to use the locale method for the first time today really appreciate your channel Mynda!
Glad I could help 🙏
Great Madam, This tutorial is simply superb, You have explained in a very simple way to understand. Thanks again. Looking forward to many more on PQE.
Thanks a lot 😊
Cool steps, thanks mam. i dabbled with lot of options in Power Query before hitting your video. Loved your explanation and solved my problem.
Great to hear!
WOW !
I'm using Excel for years but this lesson was an eyeopener.
Thanks for this video (and all others on your channel)
Well done - you made my day 😀
Awesome to hear 🙏
Amazing - Example 5 was my issue, super easy fix!! Thank you so much!!
Great to hear!
Hi Mynda. Awesome video. Especially the last example and how to use the locale option. Keep it up.
Glad it was helpful, Rohit!
Thanks Mynda, I work with US dates all the time, and this really helps.
Glad it will be useful, Peter :-)
Thanks for showing us the versatile use of PQ. Great Video.
Cheers, Sunder :-)
Hi Mynda, quick question, your explanation is fine if you have 1 column of date formatting issues but i have multiple date columns per tab and 6 tabs. Splitting each column across 6 tabs would take me ages !! How do i re-apply the fix across multiple columns/tabs/sheets?
Hi Daniel, it really depends on how the date is formatted, but either way you'd have to apply the fix to each tab as a separate query unless you're consolidating the data into one table. It's difficult to help here, but if you post your question and sample Excel file on our forum someone can help you further: www.myonlinetraininghub.com/excel-forum
Fantastic lesson and presentation. Thank you so much! (I can't imagine what the four trolls with the thumbs down as I write this were thinking!)
Thanks so much, Dallman!
Just awesome ❤
Thanks a lot 😊
Solved my problem instantly, thanks a lot!! Btw I love your accent.
Great to hear, Cris!
Hi Mynda - great tips and tricks
Thanks for watching, Heiko!
at 7:28 example four I have this exact problem but mine will not convert like yours. I have tried every scenario and it still does not convert to a numeric value remains as text.
Hi Michael, Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Saved me a ton of work! Thanks!
Great to hear!
Brilliant video. Thank you so much. A great time saver.
Glad it helped!
Thanks for sharing this Mynda, very useful tricks!
Glad you enjoyed it!
Thank you for the video! Helped me a lot as I kept getting errors whenever I tried to swap to a whole number.
Great to hear!
I work with us date format. This is super easy formatting tips. Generally i use text function to change date day and month but i have to do it every time from us to india or india to us format.
Thanks a lot mam
Glad you found it helpful, Deependra. I would avoid using the TEXT function because then the dates are not usable in formulas or PivotTables etc.
I didn't know that you could split columns by specifying the quantity as in your example (0.2,4). Very useful, good tips. Thank you very much Mynda!
Glad it was worth your time to watch, Luciano :-)
Great examples of date transformations!
Glad you like them, Doug!
Is realy need .......Date is big matter madam ...... U r excel The Boss
Glad you liked it 😊
Learned something new today, Thankyou
Glad to hear it!
Thanks Mynda that's very helpful. I have file coming to me from Project Managers all over the world and so from mutiple locales. They are all text fields. Is there a way to process data from multiple locales into the format in my locale. Concretely, I have text fields from UK in 01122020 format and12012020 format. Thanks
should read " and from US in 12012020 format"
Great to hear it'll be helpful, Seamus! For the different locale files you'd need to change the type using locale on each individual file before appending them into one consolidated table.
@@MyOnlineTrainingHub Thanks Mynda - i was afraid of that. :)
Brilliant, Thanks Mynda 👍👍❤❤
Glad it was helpful!
Very detailed explanation.. Thank you very much...
My pleasure 😊
You are a life saver. Thank you so very much!
You are most welcome 😊
I was looking for a video like that, thanks you because this is so useful
Glad it was helpful!
Super helpful! Very clear and useful!
Glad you think so!
Thanks for the lesson - learned a lot.
I have a Question about the Import.
My csv Data shows the Date like 02.01.2021 (EU 02.Jan, 2021) when i load in my files to Power Query it shows me a text 2012021 - other Dates like 30.01.2021 will show up as 30012021 (1 more Number)
With both i have wrong data with Dates in future or an "Error" is showing up - is there any fix to that Problem? - or is there a way to tell power query that he just have to import the data without removing something like the Dots or the Zerro in front?
Thanks a lot
Hi Christian, You can use Text.PadStart to add a zero to the dates 1st to 9th of each month. Then you can split the result into the 3 date components (day, month and year) and rebuild the date using the #date function. If you get stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda!Really Great Helpful Tips.Being Able To Change Locale Is A Godsend...Thank You :)
Cheers, Darryl!
I have a question. What happens when you have a row of promoted date headers that need to be changed from text? Right now, I just convert them in excel to become the right data type so that I can use Index/Match/Match to make a report. Wondering how that one row gets converted into the right date format so I can avoid the extra step in Excel.
Ideally, you should never use dates as headers. Sounds like your data needs unpivoting into a tabular layout: ruclips.net/video/-IMqkg35adA/видео.html
Hello, Mynda. Just wondering what would be the solution to dates formatted as 2/6/2018, 6/2/2018, 06/02/2018, 02/06/2018, 6/2/2018, 2/6/2018, 6/02/2018 into one date format as there have been different format texts from a file in the past I'm keen to convert into DD-MM-YYYY? Length of texts is 6,7,8,10 digits if I replace /. For example: Some of them formatted as the month in very left side while other months are positioned in the middle.
Hi Bilguun, you can't do this in Power Query because all dates in the column aren't the same format i.e. either text or number. Instead, in Excel add a column that determines whether the dates are text or not using the TEXT function. Then sort the column based on the result. You can then isolate the text values and fix them using one of the options suggested here: www.myonlinetraininghub.com/fixing-excel-dates-formatted-text
@@MyOnlineTrainingHub Thanks, Mynda. What I did was first I separated them using text to columns, then using a formula that will merge them together for example, if F,G,H columns contains either MM,DD,YYYY or DD,MM,YYYY, used =F2&"/"&G2&"/"&H2 formula to join them, then using =date value, lastly format cells so I can convert them to my desired format which is DD/MM/YYYY, but came back as multiple errors as a few of them formatted in 10 digits like 1/2/2020 which could be 01/Feb/2020 or 02/Jan/2020. Pretty long process, isn't it? If I could use just one formula which will isolate them while putting them to only one format would be fantastic... Not sure if this could be possible but what about Using IFERROR, IF formulas to put these bad guys into one column or even with VLOOKUP just for the sake of it would do the magic and if anyone knows how to, your assistance on this matter will be invaluable. Cheers
Mynda, I've come across dates where its shown as August 1st or July 2nd etc. I've been using Replace to remove st, nd, rd, and th from the dates and then a simple date format change is done, but I'm wondering if you have a simpler way of formatting this?
You could probably use Split to separate the last 2 characters in one go.
@@MyOnlineTrainingHub I should have said my dates show as January 4th, 2021. At the moment I find if I remove the nd/rd/th with replace it works fine but with "st" because of August ending with "st" I need to use a replace of "st," to save the spelling of August. Any other suggestions?
This helped me today. Thank you
I'm so glad!
Thanks Mynda. It's a really great content. God bless you!
Thank you, Alejandro!
I want to learn more about the dax formula that you've done in dashboard post. Could you please send me a relevant course, please? I might learn it over the Christmas break.😁
Great to hear you want to learn DAX. You can do so in my Power Pivot course: www.myonlinetraininghub.com/excel-dashboard-course
@@MyOnlineTrainingHub - Thank you
YOU ARE A GENIUS!
So pleased you found the video helpful 🙏😊
Este es el mejor video para transformar fechas en formato texto. Gracias por compartir conocimiento
Glad you found it helpful!
@MyOnlineTrainngHub - Awesome stuff as ever Mynda. Thanks for sharing. Take care and stay safe!
Thanks! You too!
Very useful for those who work with system generated reports since those exported report have non compatible date formats with excel.
Glad you liked it, Nipun :-)
Brilliant! Solved my issue. Many thanks 🙏
Great to hear!
Solved so many issues. Thanks.
Cheers, Chris!
Many thanks. It's really a great lesson.
Glad you liked it!
Many thanks i have date issues in large files and this will be very helpful
Pleased to hear that, Clive!
Incredibly useful 👌
Great to hear!
Hello, I have a data source where the date is written as 1st, 2nd, 3rd... of each month and year. The Date.FromText function returns an error. Is there an easy way to convert a string such as '8th November 2023' to a date?
I often watch your Excel videos and find them very informative, so thank you for the very useful guides which you produce.
Hi Nigel, you could split the number from the 'nd' 'rd' etc. text or replace these text strings with nothing to isolate the number, then it should convert to a date more easily. If you're stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub .. Thank you.. yes, I did a bit more investigating and splitting the text into constituent parts does seem to be the only method, then applying several steps to remove the ordinal suffixes, then recombine into a date string which Power Query can handle. It does seem like a curious oversight.
Hello, Really amazing! this video really helped me fix my date problem in power BI. Thank you so much!
Great to hear!
@@MyOnlineTrainingHub
Hi,
What are your courses listed on UDEMY?
Thank you!
Hi Mamadou, thanks for your interest in my courses. My Udemy courses are out of date. I'm not using that site anymore, but you can get all my courses from my own site here: www.myonlinetraininghub.com/
Thank you so much. This is very helpful.
You're very welcome!
Hi Mynda, great video! I have a question: how can I calculate the time (duration in hh:mm:ss) in Power Query / Power BI? For example, I need to add working hours for different projects. In my Excel file, I have the duration for each activity. When I work with my table in Power Query or Power BI, the format change into a date-time format and I can't do calculations with that.
Thanks, Dan! To answer your question, you can change the data type to Time in Power Query.
@@MyOnlineTrainingHub thank you Mynda
@MyOnlineTrainingHub get Workbook link is not working, how do i get the practice file, please help..
I just tested the link. It takes you to the written tutorial. You'll find the workbook download link on that page under the video.
@@MyOnlineTrainingHub Madam yes madam, it directs me to that link and asking to enter my email address even after providing email, I am getting the download. please provide the practice file. please madam.
Thx for this lesson! You're really Beauty and a Beast (in Query) :)
😊 Glad it was helpful!
Thanks Mynda. That was very helpful!! :) :)
Pleased to hear that, John :-)
Thanks Mynda - i get dates from my US colleagues and I always have a struggle when I try to integrate with my dates here in Ireland.
Glad it was helpful, Seamus :-)
Thanks. I always felt like col from example was cheating but I'll use it as a quick fix for date formatting!
Glad it'll be helpful!
As a Canadian who's lived in the UK and US, I've run into this issue for decades, esp the last 2, after incorporating one of our cos in the US. So I've got bank records with both, often intermingling in the same database (with a USD to CAD conversion column), and have used a variety of workarounds.
Last year I watched your wonderful channel and starting using Power Query. Thanks for that!
However, today I went to transform some dates listed 'dd/mm/yyyy' by using the LOCALE method, and after selecting DATE I chose 'English (Canada)'. The result was not what I expected (as here in Canada we've grown up with day month year): all of those dates in the 'mm' position were deleted (I forget what PQ called them) leaving me with only the dates that had month values at 12 or less. Working on a theory that Canada may have finally been subsumed into USA accounting formats I tried selecting 'English (Australia)' instead: AND IT WORKED!
We need some sort of international agreement: For the last two decades I've been converting ALL dates to DD-MMM-YYYY [if that's how you get 21-MAY-2022] > I realize it's just so we can deal with the yanks, but what else to do?
Glad my video was helpful! I don't envy your date experience.
Thanks! Very useful 😄
Glad to hear that, Ian!
Very helpful, thank you.
My pleasure, Keith!
i have a column that contains data like jan, feb, mar but this column not converting into date format in power bi power query
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Brilliant video thank you
Glad you enjoyed it 😊
Thank you very much, this helped me a lot.
Glad to hear that!
I found this lesson extremely helpful. However, I have 2 quick questions: in the custom formating option, why the month and year have more letters than day like this: d/mm/yyyy. My 2nd question is that I notice there is a date/time/timezone option in the power query but don't know why you don't select it instead.
Glad you found my video helpful, Toan :-) To answer your questions:
1. d/mm/yyyy will format dates with a single digit day with one character e.g. 1/10/2020 whereas dd/mm/yyyy will format it 01/10/2020
2. Date/Time/Timezone format requires the timezone to be part of the value e.g. how many hours from UTC the time is. More on date timezones in Power Query here: docs.microsoft.com/en-us/powerquery-m/datetimezone-from
Stuck on the first example ma'am. It keeps returning "Error" for the merged column after trying to change the data type from number to date. I'm confused.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda! Thanks a lot for sharing your knowledge! It’s really helpful and I really like how you’re always using shortcuts =)
So pleased to hear that, Maria!
I download csv file from my bank and this one bank's date always causes an issue which after watching your clear and concise video, still has yet to solve the issue. I finally just typed out the dates to get it to work (time consuming from all angles). I would love the silver bullet on these dates.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
This was so beautiful because I was stuck in trying to change January to Jan and February to Feb but when you said it is done in excel and not in Power Query. I thought OMG now I know why I got stuck. Didn't know that you can't change full month names to shorter names in PQ. Why did you put 0,2,4 for Positions? This is confusing. In my example, some are 11/29/2019 and some are 1/7/2018 so what would be positioned for these examples? Thanks
Glad you found it useful! The 0,2,4 example only applies to dates in this format: 18072020 or 01122020 etc. as in the example shown i.e. where there are no delimiters and the structure is ddmmyyyy or mmddyyyy. If you have delimiters, like the forward slash, then you would use one of the other techniques I showed.
@@MyOnlineTrainingHub OK! Thank You!
@@MyOnlineTrainingHub You know what? I was able to get shorter months from Power Query. Someone told me that I will need to manipulate M Code and that is what I did and I was able to get Shorter months from PowerQuery. Thought of letting you know that you can do it in PowerQuery. Thanks
Yes, you can, but those months are now text and therefore not usable in formulas, nor will they sort properly because Excel cannot understand them as calendar periods. This is why formatting like this is best done in Excel using cell formatting on date values.
thank you sharing, this one was very helpful
Great to hear!
Madam neither of the link is working will you please help me out, i want the practice file for practicing please help me
Sorry about that, please try again. I've fixed the issue.
@@MyOnlineTrainingHub Madam thank you very much, its working now and i have downloaded the same. Thanks a ton, many thanks madam.. thank thank you :)
You are amazing!! Thank you so much
Glad I could help :-)
Hi Lynda,
What I want to learn is that since there are Microsoft engineers who developed the DAX software language and have a much broader formula and data analysis power with the use of DAX, why do the formulas we use in excel have such limited capacity?
More precisely, when there was a powerful analysis program like Excel, why was the need to develop a program like PowerQuery and use a different software language?
Thank you
Hi Emre! DAX is designed to be used on relational data, whereas Excel functions are not. This new capacity with Power Pivot required a new language that's design is modeled on the familiar Excel functions. Keep in mind that Excel is built on millions of lines of 35+ year old code. It's not so easy to just change, but we're seeing massive improvements to Excel functions e.g. cells containing rich data types, dynamic arrays, LET and LAMBDA to name a few and there's more coming.
Power query is a different tool, it's all about getting and transforming data and that requires a more advanced coding language than Excel functions can give us. The Excel team did a great job of automating the writing of this code by giving us a nice GUI that allows us to click some buttons and have the code written for us.
Wow, this has really helped me
Awesome to hear 😊
Can we do this for month year data, Ifor example 012020 Wer 01 month followed by year
Yes, but you'll have to add a value for the day e.g. the 1st of each month.
Thanks Mynda. PQ rules!
It so does, Chris :-D
Thank you this video is helpful 😊
I'm so glad!
Great tutorial! Thanks :-)
You're welcome!
Mil gracias por tu ayuda !
My pleasure 😊
Thank you so much for sharing this. It is super useful indeed.
Great to hear, Claire :-)
Thank you so much!
You're welcome!
I'm always waiting for ur great content like this
Glad you enjoy it!
very useful thanks a lot
You are most welcome, Mohamad!
Thank you so much Mynda! I've been struggling with the last form of date conversion every month for years now and never thought to try PQ... it works flawlessly!
So pleased to hear this video was helpful :-)
Thanks Mynda 👍🏻
My pleasure, Graham :-)
how about to convert unix timestamp format to short date?
I vaguely remember something about Unix timestamps, but not enough to do an example, sorry.
How to change the month number to month name in power query ex 9 as Sep, 10 as Oct.. all I want changes to be done in same column
Hi Kiran, this is a format which is applied in the Excel worksheet after 'closing & loading' the query. e.g. dd mmm yy
More on custom number formatting here: www.myonlinetraininghub.com/excel-custom-number-format-guide