what about goin g the other way around? Lets say we have the green table from 8:11 and we want it to convert itto blue table? how would we go about it? the same but choosing different settings in localle?
It is because I have used Excel so long and remember when we did not have these features, and we had to update manually... It was so tedious and took so long. Now... when I use our new tools Power Query, Power Pivot, Data Model and Poser BI, I just can't believe how fast and easy it is to update : )
I come to know about power query from watching Miss Leila Gharani's short video, then I need to learn more, I need more knowledge. I searched and reached the best one. Thanks Leila, Thanks Mike & Bill. You people helped me a lot. God Bless you !
I have to say Mike, this if flipping awesome! I really appreciate you and Bill for laying out these advanced features of my two favorite programs! Cheers
Hi. Thank you for the video. French(France) locale worked for me for the Date column and I used French(Canada) locale for the Sales column to work (in both cases: video and homework).
As usual Excellent video.... You have taught everything about excel..... Excel, Adv Excel, Array Formulas,Pivots, Power Pivot, DAX, Power BI....... LEAVING out VBA :D ... I guess VBA would be thinking what wrong did I do in this life... haha...
¡Bienvenido al video, ladio gonzalez bardelli! ¡Me alegra que puedas ver esto en Chile! Gracias por su apoyo con su comentario, Thumbs Up and Sub : ) Asegúrese de decirle a todos sus amigos y colegas en Chile que el excelente canal aquí en UA-cam es gratis, divertido y eficiente : ) You are welcome for the video, ladio gonzalez bardelli!!! I am glad that you can watch this in Chile! Thank you for your support with your comment, Thumbs Up and Sub : ) Be sure to tell all your friends and colleagues down in Chile that the excelisfun channel here at UA-cam is free, fun and efficient : )
You are welcome for the unbelievable, free lancer!!! Thanks for your support of MSPTDA and excelisfun with those comments, thumbs ups and of course your Sub : )
Mike, I did not quite get the homework. So all we had to do was change the date format of the table in Excel and not PQ? If so, how would I do the same in Power BI since it does not have any such date formatting options in its basic UI?
Thanks Mike, Great solution. Those were many, many tricks on PQ. Now looking forward to Power Pivot and DAX. Since you talked about the ISOdate,, I was also looking for ISO weeknr. In Europe you have to use in Excel: Weeknr(date;21). Where 21 is the "optional" code to specify which system you use. In older Excel versions, you can only choose for 1 or 2. However in M, this is different. I compared this with Excel and came to following result: Power Query 0 = the 1 in Excel, PQ 1 = 2 in Excel. I saw some post on the internet how to enter correct weeknumbers. At this moment I must say that Excel has a better weeknr formula than PQ. Why is this all relevant? Well maybe you recieve files with weeknrs from different regions and then you have to harmonize them. Greetings, Bart
Great video, for some reason when I tried the locale method it didn't work. My computer settings are English Ireland, my csv are English USA, I selected English USA (my understanding from the video is you select where it's coming from). Alas it didn't work but I'm going to try again. The me function looks great, little over my head exactly how it works but look forward to figuring it out ;)
A lot. Check out MSPTDA playlist: ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html Power Query Playlist: ua-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html I look forward to your comments and Thumbs ups : )
Thanks Mike! Very interesting and very handy. How I could have used this ability to easily automate locale transformations when some twenty plus years ago when I did indeed receive a daily load of similar file data from various places in Europe. Would have been cool - IS cool today! Thanks and excited by the next 3 videos coming.
Yes, for so many of us, it is just amazing what Power Query can do, and it really replaces the decades of the more complicated steps that we used to do. It is just more evidence in the torrential flow of reasons that all data analysts MUST learn this new tool: Power Query / Power Pivot / Power BI Desktop : )
Thanks for that hot tip, JNT Consulting!! I did know that, but I wanted to show that you could type a table, like in Excel when we use Excel Table Feature.
Hi Mike, that is brilliant and really helpful. One question though, what if I am not sure from which country that data would be from. I mean, just got a csv file with a different date format. Do we have an international format compatible with all Regional Setting?
If we don't know, we don't know. I am not sure what to do, but look at the dates in the data set, see what the pattern is, and look at what we have in Regional Seetings.
Mike,,, great example specially for people that deals with dates from other countries. In your example, you show how to transform the date from 22/01/2018 to the US format 01/22/2018. Is it possible to still see the 22/01/2018 as a number so both date columns would be 22/01/2018 and 01/22/2018 respectively? Thanks
Curiosity killed the cat, but not me! Question: What monetary unit are the sales figures from countries other than USA being reported in? If it's US Dollars great, but you didn't mention what the monetary unit was, and since the date format wasn't being reported as a US date format, it would lead me to suspect the verasitiy of the monetary unit. Thanks for the great video Mike, I have never felt truly geeky until I started to absolutely love learning MS Excel at this level.
hi,I have one question not related to this video but I had to ask, I am importing data from web, the problem is, I am using tempermonkey on my browser and with that I can run scripts on my webpage,and I could see some additional data on my webpage.but when I import it into excel, excel doesn't show additional data that was in website because of script .i tried a lot. how we can import from the web to excel when there are scripts running on that site? tnx
@@excelisfun thank you, these days, many companies run scripts on their (online)database websites for showing extra data .espicially logistics company like Amazon.when you import that page in excel with web query you can't see the extra data, anyways thanks again for your response.if I find the answer i will inform you about it .
On 1/21/2023 using Insider edition of 365 (Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16110.20000) 64-bit ), I had to remove the spaces in the France Sales column before using Locale. Without doing that first, those numbers produced errors. Don't know if that's a temporary bug or how it works now! This had to be done in the Power BI Function as well.
hi Mr. Mike i don't know what's wrong i didn't receive alert that you posted a new video I just checked your channel and got this one i'm gonna see it now
Respected Sir, how I can extract hh:mm:ss to separate columns by using formula from any date and time stored as following format 9/28/2018 12:10:54 PM? for example drive.google.com/file/d/1mEjsIGp5oFqtWR5CvHI1zy3ls_-m_tFr/view?usp=sharing
i have column A with timestamp, column B with username, column C with Actions like Login, logout, the problem that i am facing that sometimes you can have 2 or 3 logins for the same user in the same day, when i do look up it always show the first match or the first login and first logout, i want to lookup and find the 2nd and 3rd match and so on with multiple criteria which are ( username and login - username and logout ), i can show you a sample sheet just tell me how to send it to you, PLZ i really need this PLZ help me
What tool are you trying to do this in? Excel, Power Query or DAX? As I said, I am very busy right now. No worries, though, try posting your question to this great Excel Question site: mrexcel.com/forum
I am really disappointed, because I consider these videos highly interersting but, definitively, files links, to follow allong the classes, not work!...
Homework Files are posted. Have fun practicing with the homework file : )
Great stuff :)
what about goin g the other way around?
Lets say we have the green table from 8:11 and we want it to convert itto blue table?
how would we go about it? the same but choosing different settings in localle?
Thanks Mike!!! Absolutely perfect explanation of "Locale" !!
Well... I owe it all you you for teaching me so well, O Poet of Power Query : )
I love how you react everytime PQ/PB updates itself with new text data!
So wholesome!
It is because I have used Excel so long and remember when we did not have these features, and we had to update manually... It was so tedious and took so long. Now... when I use our new tools Power Query, Power Pivot, Data Model and Poser BI, I just can't believe how fast and easy it is to update : )
Yup, your excitement is very contagious. Helps us stay enthusiastic!
@@mahneelmusa7469 Go Team!!!!
Amazing! Thank you Mike & Bill. This is really handy!
You are welcome, teammate Leila!!!!
I come to know about power query from watching Miss Leila Gharani's short video, then I need to learn more, I need more knowledge. I searched and reached the best one. Thanks Leila, Thanks Mike & Bill. You people helped me a lot. God Bless you !
Feeling blessed to know I am at right place
Absolutely amazing examples for using locale. This is something i was using before but you have helped me unlock its full potential.
I have to say Mike, this if flipping awesome! I really appreciate you and Bill for laying out these advanced features of my two favorite programs! Cheers
Hi. Thank you for the video. French(France) locale worked for me for the Date column and I used French(Canada) locale for the Sales column to work (in both cases: video and homework).
This is actually a practical one for me, as I have to handle data from Germany, France and UK...etc. Thank you and this is a great one!
You are welcome! Thanks for the support, rf05mjy!!!
Excellent work, I had written VBA to solve the ISO date problem, this simplifies it.
Thanks Mike for sending it over to all of us and thanks to Bill Szysz. :) :)
You are welcome, John! Go Team!!!
Thanks for another amazing video Mike! And thanks to Bill "Power" Szysz !
You are welcome, Teammate Kevin!!!
Another great video from Mike for us Excel lovers. Thanks Mike.
You are welcome! Thanks for the support, M. SZ.!!!
As usual Excellent video....
You have taught everything about excel..... Excel, Adv Excel, Array Formulas,Pivots, Power Pivot, DAX, Power BI....... LEAVING out VBA :D ... I guess VBA would be thinking what wrong did I do in this life... haha...
Yes, no VBA... Glad you liked this video, though : )
I was wondering about Locale few days ago and here it is. Thank you very much.
I think you might have received my subconscious message. :)
Yes, you wondered and i made a video !!!!! Thanks for the support, Nis 007!!!!
@@excelisfun There is one problem i commented please do check that out. That problem really bugging me. On Excel Magic Trick 1415.
Love your videos Mike
Glad you like them, walid!!!
Thanks to you and to Bill Szysz
You are welcome! Go Team!
I love Power Query.
Now, I need learn Power Pivot.
Thank you
The next 10 or so videos will be about Power Pivot. It is just slow to make the whole class from scratch : ) Thanks for your support, linelson!!!!
What a fantastic job. Thank you.. I will be taking all your online courses for sure.
You are welcome, Vimal!!!! Thanks for your support as you watch and learn with comments and thumbs up, and of course your Sub : )
You are query king👑❤️
"Thanks for being so dedicated and hard-working!" I wish i will follow you :)
wuow fantastico tips, gracias saludos desde Chile, from end the world.
¡Bienvenido al video, ladio gonzalez bardelli! ¡Me alegra que puedas ver esto en Chile! Gracias por su apoyo con su comentario, Thumbs Up and Sub : ) Asegúrese de decirle a todos sus amigos y colegas en Chile que el excelente canal aquí en UA-cam es gratis, divertido y eficiente : )
You are welcome for the video, ladio gonzalez bardelli!!! I am glad that you can watch this in Chile! Thank you for your support with your comment, Thumbs Up and Sub : ) Be sure to tell all your friends and colleagues down in Chile that the excelisfun channel here at UA-cam is free, fun and efficient : )
Many great tricks! Thanks!
You are welcome, Most Awesome Teammate pmsocho!!!! Thank you for your support : ) : )
Awesome explanation Mike...great video...just unbelievable...Thanks for the video...:)
You are welcome for the unbelievable, free lancer!!! Thanks for your support of MSPTDA and excelisfun with those comments, thumbs ups and of course your Sub : )
Fantastic as always. Thanks very much for this great work Mike. Looking forward to more :-).
You are welcome! More fun to come! Thanks for your support : )
Wow! It solves a lot o date problems! Thank you, MIke!
BTW, the ISO Date solution is awesome.!! Thx
Glad it is helpful, Matt!!!!!! Thanks for your support : )
Mike, I did not quite get the homework. So all we had to do was change the date format of the table in Excel and not PQ? If so, how would I do the same in Power BI since it does not have any such date formatting options in its basic UI?
you gotta be kidding me. I spent huge amount of time and effort not knowing this.
Thank you, as always. It is amazing what Power Query can do... :)
You are welcome! Yes, Power Query is soooooo amazing : )
Thank you very much Mike!
You are welcome very much, Shehan!!
Amazing☺️🙏
Yes!!!!!
OMG thank you so so much Mr. Mike you are really my idol
Thanks Mike, Great solution. Those were many, many tricks on PQ. Now looking forward to Power Pivot and DAX. Since you talked about the ISOdate,, I was also looking for ISO weeknr. In Europe you have to use in Excel: Weeknr(date;21). Where 21 is the "optional" code to specify which system you use. In older Excel versions, you can only choose for 1 or 2. However in M, this is different. I compared this with Excel and came to following result: Power Query 0 = the 1 in Excel, PQ 1 = 2 in Excel. I saw some post on the internet how to enter correct weeknumbers. At this moment I must say that Excel has a better weeknr formula than PQ. Why is this all relevant? Well maybe you recieve files with weeknrs from different regions and then you have to harmonize them. Greetings, Bart
Thank you, Teammate Bart for that helpful tip about week number! I am also looking forward to Power Pivot, Power BI Desktop and DAX : )
Very helpful, nice video Mike!
Glad you like it, Chris! Thanks as always for your support : )
Thanks Mike for this video, is it possible to get locale currency symbol if we set data type "Currency" to "Sales" column.
that was great. Thanks Mike
Great video, for some reason when I tried the locale method it didn't work. My computer settings are English Ireland, my csv are English USA, I selected English USA (my understanding from the video is you select where it's coming from). Alas it didn't work but I'm going to try again. The me function looks great, little over my head exactly how it works but look forward to figuring it out ;)
Thanks for this vídeo Mike :)
You are welcome! Thank you, Excel y Mas, for your support : )
Thanks, Mike, Great video like always
Glad it is great as always, K B!!!! Thanks for your support : )
Fantastic Mike!
Glad it is fantastic for you, Armond!!! I appreciate your consistent support : )
Thank you Mike :)
You are welcome, Filip, Using Locale... is a real helper sometimes : )
Thanks Mike for EXCELlent video and the homework :P
You are welcome, Syed!!!! Thank you for your EXCELlent support : )
Thanks Mike. Very helpful as usual.
Any chance to record and publish your videos in 1080p or even 4K quality in the soon future?
Thanks alot again.
Excellent Stuff
Thanks for the video Mike. You have any more videos on changing the M Code/Custom Functions?
A lot. Check out MSPTDA playlist: ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
Power Query Playlist: ua-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html
I look forward to your comments and Thumbs ups : )
Thank You Sir
You are welcome, M. Alomery : ) SQRT(144) is an important one : )
@@excelisfun Sure :)
Thanks Mike! Very interesting and very handy. How I could have used this ability to easily automate locale transformations when some twenty plus years ago when I did indeed receive a daily load of similar file data from various places in Europe. Would have been cool - IS cool today! Thanks and excited by the next 3 videos coming.
Yes, for so many of us, it is just amazing what Power Query can do, and it really replaces the decades of the more complicated steps that we used to do. It is just more evidence in the torrential flow of reasons that all data analysts MUST learn this new tool: Power Query / Power Pivot / Power BI Desktop : )
Great Mike, Thanks. :) :)
You are welcome, John Borg!!!!
I have a problem here in power query. When I append 3 sheets from another file & merge it I found some duplicate data here. So please help me Sir.
This was amazing!
Mike, not sure if you know that but you can copy and paste when creating table in Power BI instead of typing
Thanks for that hot tip, JNT Consulting!! I did know that, but I wanted to show that you could type a table, like in Excel when we use Excel Table Feature.
Can you re-do the video for those of us who do not have Power BI. The steps seem not to match up for Power Query.
Hi Mike, that is brilliant and really helpful. One question though, what if I am not sure from which country that data would be from. I mean, just got a csv file with a different date format. Do we have an international format compatible with all Regional Setting?
If we don't know, we don't know. I am not sure what to do, but look at the dates in the data set, see what the pattern is, and look at what we have in Regional Seetings.
Dear sir can you please teach how to create a roster maker so that once we add the pre-approved leaves, it creates roster for the month
Mike,,, great example specially for people that deals with dates from other countries. In your example, you show how to transform the date from 22/01/2018 to the US format 01/22/2018. Is it possible to still see the 22/01/2018 as a number so both date columns would be 22/01/2018 and 01/22/2018 respectively? Thanks
I do not know how to do that and then combine them. You could maybe have separated columns and have one formatted as text...
Thanks Mike. I will try that...look forward to DAX and powerpivot!
good
Curiosity killed the cat, but not me!
Question: What monetary unit are the sales figures from countries other than USA being reported in?
If it's US Dollars great, but you didn't mention what the monetary unit was, and since the date format wasn't being reported as a US date format, it would lead me to suspect the verasitiy of the monetary unit.
Thanks for the great video Mike, I have never felt truly geeky until I started to absolutely love learning MS Excel at this level.
Yes, I should have mentioned monetary unit. I assumed that all the numbers had been converted to US dollar.
Amazinggggg
: ) : ) : )
hi,I have one question not related to this video but I had to ask, I am importing data from web, the problem is, I am using tempermonkey on my browser and with that I can run scripts on my webpage,and I could see some additional data on my webpage.but when I import it into excel, excel doesn't show additional data that was in website because of script .i tried a lot. how we can import from the web to excel when there are scripts running on that site? tnx
I am sorry, I have no idea how to solve that.
@@excelisfun thank you, these days, many companies run scripts on their (online)database websites for showing extra data .espicially logistics company like Amazon.when you import that page in excel with web query you can't see the extra data, anyways thanks again for your response.if I find the answer i will inform you about it .
@@javadkhalilarjmandi3906 Yes, yes, yes, please do let me know!!!
On 1/21/2023 using Insider edition of 365 (Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16110.20000) 64-bit ), I had to remove the spaces in the France Sales column before using Locale. Without doing that first, those numbers produced errors. Don't know if that's a temporary bug or how it works now! This had to be done in the Power BI Function as well.
I too face the same issue. hope to contact you to sort this out
hi Mr. Mike i don't know what's wrong i didn't receive alert that you posted a new video I just checked your channel and got this one i'm gonna see it now
To be altered, look below the video at the Sunscribe button, and next to it is a bell icon. Click that and you will be notified.
i did from the very first time I knew your channel but i don't know how that happened next video will net me know.
Respected Sir, how I can extract hh:mm:ss to separate columns by using formula from any date and time stored as following format 9/28/2018 12:10:54 PM? for example drive.google.com/file/d/1mEjsIGp5oFqtWR5CvHI1zy3ls_-m_tFr/view?usp=sharing
how can i send you a sample data with a question and you can make a tutorial about it?
I have about one year of videos already planned, so I am sort of backed up. What is your question?
i have column A with timestamp, column B with username, column C with Actions like Login, logout, the problem that i am facing that sometimes you can have 2 or 3 logins for the same user in the same day, when i do look up it always show the first match or the first login and first logout, i want to lookup and find the 2nd and 3rd match and so on with multiple criteria which are ( username and login - username and logout ), i can show you a sample sheet just tell me how to send it to you, PLZ i really need this PLZ help me
What tool are you trying to do this in? Excel, Power Query or DAX? As I said, I am very busy right now. No worries, though, try posting your question to this great Excel Question site: mrexcel.com/forum
just formula index and match
Yes, post to mrexcel.com/forum there are 100s of masters that can have back and forth dialog to get you a solution : )
when you gonna write new Book it be exceeding
I am not sure, I am having too much fun making videos : )
The pdf notes below each video in this class are like my free book to everyone.
you are awesome
Hai
I am really disappointed, because I consider these videos highly interersting but, definitively, files links, to follow allong the classes, not work!...