The comparing two lists is a good reminder that I have been under utilizing power query because a lot of my old and cumbersome ways still work and are so familiar to me. I turn to them not because they are the best but because I sometimes don't have the time or energy to learn a new way to do something I already know how to do. But it is totally worth the effort to learn a new mew approach when it is clearly a much better method. I just need to form a new habit. Thank you
Excellent intro to some common scenarios where Power Query is quite useful. I've been trying to get my coworkers to learn Power Query, so will pass this URL on to them. Thank you, Mynda!
Hello, Treacy. Great teaching here. I found the suggestions very helpful, and Power Query is an essential tool that empowers any & every Excel user. However, I realized an impediment. The Table/Range option in the Get Data ribbon is unavailable on MacOS (I used my Windows to test your solutions), and I use 365. Would you happen to have any suggestions on how to navigate that? Thank you
Glad it was helpful. Power Query on the Mac is still in development. There's no workaround for the limitations there, other than the one you've already used.
🥰 That’s really helpful. Thanks so much! When I combined the files using power query and each file has different sheet names, I ended up with error.. until I renamed the sheet name of all files with the same names (i.e. Sheet1). Do you have any advice for this? Thanks a lot!
Great to hear. This post explains how to handle sheets with different names: community.fabric.microsoft.com/t5/Community-Blog/Combining-multiple-Excel-files-with-different-sheet-names/ba-p/3003946
Great tips Mynda , Pq Connect to folder and then combining is the best feature in pq, do you have a video on how to combine files with different columns ?
Excellent as always Mynda, thank you :) Question: do you regard Excel as being robust enough these days to function as a database? Not huge DBs, my biggest table is 200K rows, updated regularly from data cleaned in XL. So workflow XL> Access > XL. I've used XL for 30+ years and it used to be too 'fragile' for DB use-just too easy to break the data. So I've used Access for 20 years to store data and XL to analyze and present.
Yes. Particularly if the data is already in Excel, then I wouldn't bother loading it to Access and then back again. If you want to make the data more robust, store the raw data in a file that isn't used for anything other than storing the data, then use Power Query to connect to that file and bring the data into another file where you clean and transform it if required, before loading it to Power Pivot. Power Pivot enables you to create relationships between tables much like Access, then analyse and summarise the data using PivotTables.
Hello, I wonder if we can use the Today's function to create a review date conditional formatting on MS Lists Example: Conditional format policies based on review date. For Review Within 6 Months For Review Within 1 Year For Review Within 2 Years For Review Within 3 or more Years. Thanks you
Yes, absolutely. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
For #3, I am in a similar situation but the files I need are in sub folders of one folder. Each folder is named for a supplier and so is each spreadsheet. When I import them I can't tell which supplier they relate to as PQ doesn't include the file name in the resulting table. Is there a way around this, please?
Hi I am trying to create a dashboard on excel and would like to know if anyway possible that I create it on excel but hide sheets so that it looks like it is created on some application? Please advise I have very less that that is why not going for POWER BI and also need to use some userform. Any help will be appreciated. Thanks
A question. In the second example, why did you create a new step by renaming columns instead of changing the auto generated names inside the split columns step?
❓ What’s your favorite Excel trick for saving time?
Power Query course: bit.ly/powerq24course
Thank you!
The comparing two lists is a good reminder that I have been under utilizing power query because a lot of my old and cumbersome ways still work and are so familiar to me. I turn to them not because they are the best but because I sometimes don't have the time or energy to learn a new way to do something I already know how to do. But it is totally worth the effort to learn a new mew approach when it is clearly a much better method. I just need to form a new habit. Thank you
Hope you can try it next time 😊
@@MyOnlineTrainingHub I'm going to try it tomorrow! Thank you so much!!
Such a gifted teacher, thanks for sharing!
Thanks for your kind words!
Power Query is the magic in the kitchen......love it . Thank you my friend
Indeed it is, Mo 😉
I think I landed a gold mine with this channel! I love the teaching language and approach. Makes it very easy to follow.
Awesome, thank you!
Unpivoting the columns is the perfect tip for me.thank you😊
Glad it was helpful!
Every time I watch your video it mesmerized me. You are amazing.
Thank you so much 😀
I did a live demo of #3 and #5 for co-workers and they were all 🤯.
Awesome to hear!
loved the lookup trick with the inner step
Thanks so much!
Excellent intro to some common scenarios where Power Query is quite useful. I've been trying to get my coworkers to learn Power Query, so will pass this URL on to them.
Thank you, Mynda!
Glad it was helpful! Hope your coworkers love Power Query.
OMG!!! I gotta grab some real grip with PQE. It's insanely genius :) U are amazing.😃
Glad you like it! 😁
OMG! Thank u so much, this are so amazing tricks!!! Greetings from México!
Glad you liked them!
I love your channel so much. Thank you for this
Thanks so much! 🥰
Thank you for the wonderful introduction!
You're very welcome! Have fun with Power Query 😉
I'm 77 years old. Where can I download ready made files I can download and use ?
In Excel go to the File tab > New. There you will find many free templates.
Thank you!
You're welcome!
Wow! Nice Power Query video! Covering some very nice transformation!
Thank you very much!
Great. Thank you.
Thanks for watching!
Hello, Treacy. Great teaching here. I found the suggestions very helpful, and Power Query is an essential tool that empowers any & every Excel user.
However, I realized an impediment. The Table/Range option in the Get Data ribbon is unavailable on MacOS (I used my Windows to test your solutions), and I use 365.
Would you happen to have any suggestions on how to navigate that? Thank you
Glad it was helpful. Power Query on the Mac is still in development. There's no workaround for the limitations there, other than the one you've already used.
Awesome Mynda!
Cheers, Chris!
🥰 That’s really helpful. Thanks so much!
When I combined the files using power query and each file has different sheet names, I ended up with error.. until I renamed the sheet name of all files with the same names (i.e. Sheet1). Do you have any advice for this? Thanks a lot!
Great to hear. This post explains how to handle sheets with different names: community.fabric.microsoft.com/t5/Community-Blog/Combining-multiple-Excel-files-with-different-sheet-names/ba-p/3003946
Great tips Mynda ,
Pq Connect to folder and then combining is the best feature in pq, do you have a video on how to combine files with different columns ?
I don’t. Party because there are many ways they can be different so it’s difficult to cover them all!
Excellent as always Mynda, thank you :)
Question: do you regard Excel as being robust enough these days to function as a database? Not huge DBs, my biggest table is 200K rows, updated regularly from data cleaned in XL. So workflow XL> Access > XL.
I've used XL for 30+ years and it used to be too 'fragile' for DB use-just too easy to break the data. So I've used Access for 20 years to store data and XL to analyze and present.
Yes. Particularly if the data is already in Excel, then I wouldn't bother loading it to Access and then back again. If you want to make the data more robust, store the raw data in a file that isn't used for anything other than storing the data, then use Power Query to connect to that file and bring the data into another file where you clean and transform it if required, before loading it to Power Pivot. Power Pivot enables you to create relationships between tables much like Access, then analyse and summarise the data using PivotTables.
@@MyOnlineTrainingHub Brilliant, thank you!
excellent thank you very much!
Thanks for watching!
thanks
You're welcome!
Gorgeous, especially that full oter oist compare trick. Are you on Udemy?
So pleased you liked it! My courses are available here: www.myonlinetraininghub.com/courses
One question, after loading data as query through power query, can we use excel formulas as usually we do in excel tables ?
Yes, most definitely.
Boring Task Number 3. Is it possible to Get Data from various files from a SharePoint folder rather than file explorer?
Yes 😉 www.myonlinetraininghub.com/get-data-from-onedrive-or-sharepoint-with-power-query
Hello, I wonder if we can use the Today's function to create a review date conditional formatting on MS Lists
Example: Conditional format policies based on review date.
For Review Within 6 Months
For Review Within 1 Year
For Review Within 2 Years
For Review Within 3 or more Years. Thanks you
Yes, absolutely. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thank you!
For #3, I am in a similar situation but the files I need are in sub folders of one folder. Each folder is named for a supplier and so is each spreadsheet.
When I import them I can't tell which supplier they relate to as PQ doesn't include the file name in the resulting table. Is there a way around this, please?
Yes, instead of 'combine & edit' just click 'transform'. Then you can manually extract the data from the Content column and retain the file names.
Hi I am trying to create a dashboard on excel and would like to know if anyway possible that I create it on excel but hide sheets so that it looks like it is created on some application? Please advise
I have very less that that is why not going for POWER BI and also need to use some userform. Any help will be appreciated. Thanks
You can turn off the formula bar and column labels and minimise the ribbon so that there are very little Excel features visible.
Awesome.
Thanks, Robert!
Nice video :)
Glad you enjoyed it 🙏
A question. In the second example, why did you create a new step by renaming columns instead of changing the auto generated names inside the split columns step?
Because this is a video for people who haven't used Power Query before and I didn't want to intimidate them by editing code 😜
@@MyOnlineTrainingHub, thank you for the quick answer.
do we need office 2021 for all the purpose??? i just have office 2016
No, you have Power Query in Excel 2016 too.
❤
Thanks for watching!
🤗
Thanks for watching, Chris!
I'm one week into a starting pay job of 100k , trying to learn excel , and I'm 1 week away from quitting 😂
😂I'm sure with practice and persistence you'll be worth every cent of that 100k salary.
I will probably never understand excel 😂
Practice makes perfect 😜