Get all 3 courses in a discounted bundle from the Dashboard course page: 👩🏫 Power Query course: bit.ly/powerquery240319 👩🏫 Power Pivot & DAX course: bit.ly/powerpivot240319 👩🏫 Excel Dashboards course: bit.ly/dashboards240319
For anyone watching these lectures, and if you've had actual classes, you know you can't blink. So please take your time to create those pathways in your mind. Thanks for the knowledge!
Been using PQ for a couple years now and the most amazing piece was loading/cleaning and anonymising an xml source file into excel worksheet for generic viewing which took a previous incumbent 2~3 hours to complete... the PQ refresh brought it down to 7 seconds.. Absolute hero for a day😇
Learning PQ was the best thing to support my work. It gooses all results to save time and frustration. I always recommend people invest in a class. Most benefit from a structured learning approach to build confidence and efficiency.
I did the same with PQ. Took a 3 - 5 hour process down to 30 minutes. Unfortunately those that came behind me try hard to break the PQ that I built. They like to add filters to the feeder data and it makes the PQ big mad!!
@@ItsjustmeAR Perhaps you can educate downstream users to make their own PQ as a reference to the one that you created that did the heavy lifting, so they can then apply their needful filtering (if that is practicable) on the data that has been expertly conditioned by you.
I discovered PQ two years ago. Thank God I did. I am controller over 73 companies. I must create a combined financial statement for all of the companies. With PQ combining the data take just a few minutes. As you can imagine, it used to take a VERY long time.
Love this! I've just done step one, into preparing for this phase. I have taken a monthly scorecard that was complied all manually, and took 5 days! I have now built an interactive dashboard, that just needs the data adding into the background data table worksheet, which is still currently done manually. Whilst this part only takes 15 minutes to type in, it is at risk of error, and would need a peer review to verify accuracy (additional time wasted there). So, phase 2 will be to export a CSV from there database (Ideagen), and then set up a Power Query (or, potentially export direct from the Ideagen database.
Wow, I have only a basic level of knowledge in Excel, but man, am I impressed. I had no idea so much could be done. I understood much of this lesson even though I could not complete the project alone. I want to learn more and become proficient in Excel. A basic class will suffice for the time being. I am very impressed with you, and now that I understand Excel, I am even more impressed with what can be done.
thank you for this! my goal for this year is to learn PQ & BI and basically just level up on my excel skills to save more time in terms of work. You are one of my favorite guru so far, thank you! :)
Wonderful to hear! Keep in mind that to use Power BI you also need Power Pivot skills. Good luck with it. Power BI is an amazing tool. Check out this video to get started: th-cam.com/video/gOs7EC-FebE/w-d-xo.html
You are awesome!! I love Excel, and your videos are exactly what I need. You speak clearly, explain the features, provide simple and examples at a steady pace. I have watched several videos and eventually stop them b/c the trainer's voice is not clear and/or boring. THANK YOU! THANK YOU! and yes, I have subscribed :) have a great day, -i
I understood like 97% of this. I can't believe I found something I can learn and Im so fascinated by this. Like I be wanting to learn it so hard cause I never had a passion for a thing. It's achievable
That's great to hear! If you want to get up to speed quickly, please consider my courses: 👩🏫 Power Query course: bit.ly/powerquery240319 👩🏫 Power Pivot & DAX course: bit.ly/powerpivot240319 👩🏫 Excel Dashboards course: bit.ly/dashboards240319 💡Tip: Get all 3 courses in a discounted bundle from the Dashboard course page.
What a great demo! Thank you so much. One thing that tripped me up for a bit was the necessitty to copy the dimension tables from your demo file into my own workbook when recreating the data model and dashboard. Going to check out your courses.
i am big fan of your and always follow you video, I have great respect for you and learn many things from you - you are amazing and darling, the way you teach is really superb
Really valuable content. Glad I watched a second time to work through the example using the sample data to really get familiar with how slick a solution this is. Thank you!
Thank you. There's always a risk of automating yourself out of your job :) You did touch on this topic but what you said is dependent on your boss thinking forward, which not all of them do.
C011, C013 and C015 are different customers that all happen to live in Oklahoma. This table is used to lookup the customer number and then return the state they live in, so yes it needs to be there multiple times.
How cool is that ... watching my own comment on your video ! 😃 I can definitely attest to the time savings of using all the features that Excel can provide. But to do this, people have to watch your channel, keep on learning and practicing! 😜 Thanks again for the superb work you're sharing.
I love learning new Excel stuff and you are a good teacher. But there are a lot of business out there who aren't super ultra large corporations who would see you being able to get stuff done quicker and move to curtail your working hours because the company is spending too much money on you for the amount of things you get done. Could backfire. Still, I would rather learn so I can make decisions on my own as to how I do things, when and why etc.
I don't doubt this, but with these skills you're better positioned to find a company that values you than one who is only focused on their bottom line.
Love you & this series, Could you please make a detailed video on POWER QUERY & POWER PIVOTS, I know there are alot of resources out here but you are the best, Support & Love!!
Great to hear! If you want to get your Power Query and Power Pivot skills up to speed quickly, I recommend you take my courses (links below) because if you rely on TH-cam, you are bound to miss important details: www.myonlinetraininghub.com/excel-power-query-course www.myonlinetraininghub.com/power-pivot-course
Thanks for the interesting video. I understand why your didn't go into using the M language to streamline your power pivot queries and spend time on producing all of the metrics used with DAX as this is a video meant to be an introduction to the basic ideas. A couple of things I think should have been included in the video is the production of a date table to enhance the performance of DateTime functions, the datamodel can even produce a basic date table automatically, I would have also liked to see fields in the main table that should be chosen from the DIM table hidden from the client as these things are pretty basic requirements for efficient use of larger datasets.
@@MyOnlineTrainingHub i just can't believe how many people in finance don't want to learn anything since the forward slash command from lotus 1-2-3. AI will make them redundant quick so they better get on the stick.
This video was not fast paced at all. Thanks Mynda. BTW: thanks for the map chart trick - I didn’t know that one. Good stuff, not difficult at all. Thumbs up! :-)
It’s a stretch to say cutting time has a direct 1:1 impact on company expenses. I agree that the one thing that people whose job is Excel heavy can do to be a rock star is focus on efficiency - not only their own but also the user of anything they produce.
It's not a stretch at all. As a consultant, I see many inefficient ways of grabbing, presenting and uploading data into accounting systems. I have developed several models that reduced time exponentially. Saved money through reduced staff time and improved accuracy.
As a cost management accountant in a previous life, it's not a stretch at all. Everything you do has a knock-on effect. With your free time you can now work on tasks/projects that haven't been tackled because you don't have the budget for extra staff. Or you can take on tasks others were doing, enabling you to save headcount. Or you can work on other tasks that generate income.
You simply relieved my overwhelming rage to those irrelevant irritants around me, you're like a treatment of some sort of a pacifier type. Thanks ma'am. And you're a look alike of someone I remember. Just mentioning that.
Whoa!! This has increased my efficiency greatly has I can now do efficiently what too my days within 10 to 20 minutes. Thanks a lot, God blessed you. What I have another question. What about if I have multiple excel worksheet and workbook, how can I combine it into one for analysis, please can you do a tutorial about that also. Thank a lot. Waiting for your response ma.
HI! 10:11 when you connect "relationships" make connections between data sets, what is the purpose of connecting the data sets? is this effectively creating the data model? :/
Technically, the data model can be a single table. The relationships between the tables enable you to filter your data based on fields in the related tables. For example, we can summarise the sales by the Category field in the CategoryDim table. You couldn't do this without the relationship between those two tables because the 'Categfory' field doesn't exist in the SalesData table.
The map chart uses a color scale with the lower values in the paler color. You can change the color scale to use a different color for lower values rather than paler shades of the same color.
Thank you so much for the video, it is really amazing. I have one issue after cleanning the data in power quary, I incorrectly chose close and upload to power pivot, I go back to power quary, but after that I dont have " Close and Load to" , it is gray, and I only have " Close and Load". How can I find the " Close and load to " functions? Thank you.
Great to hear and well done for trying it yourself. You can access Close & Load To from the Queries and connections pane (Data tab of the ribbon) > right-click the query you want to edit the load settings for.
Hold SHIFT to select multiple contiguous buttons, hold CTRL to select non-contiguous buttons. Clear selection by selecting first button > hold SHIFT > select last button.
As a newbie wanting to learn excel 365 by buying microsoft 365 subscription, where do i start? Can u pls link me to a beginner video and intermediate. Thank u
An alternative is Power BI, but keep in mind that not all companies have the resources and budget for Power BI, so also knowing data visualisation in Excel is beneficial.
Thank you Mynda - I found your content a few years ago and everything I have seen is top notch. I've just scratched the surface with power query, experimenting with some public data sets. Where I get stuck is inconsistencies with how source data was entered, for example information that should be multiple columns becomes grouped into a single without rhyme or reason. This makes splitting columns ineffective. Is this a topic addressed in your course?
Great to hear you've found my videos helpful! I cover splitting columns in my Power Query course, yes. And if your specific scenario isn't covered, we provide support to our students so you can reach out and get help.
Hi, I love the Power Query overview. Now, I'm trying to do the same with real data and trying to connect my org chart to tie into my nps queries and I am missing a step or don't know how to incorporate my org chart into my original query.
Hard to help without knowing how your data is structured. 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
The data in the blue tables is in the Excel file you can download from the file in the video description. In that file, they're on the "Dimension Tables" sheet.
Great Content! But there is a mistake in the calculation shown in the Days to Ship column, when you used Power Query. The difference between 01/01/2021 and 03/01/2021 is 59 days. Not 2 days
I know how to do everything in this video. What positions/roles should I be searching for on LinkedIn, Indeed, or Upwork? Have had no luck looking for analyst positions on Indeed and LinkedIn for months now...
The key with these platforms is to build up a profile and ratings so that you have some credibility. As a result, I would apply for anything you think you can do, and price yourself very competitively to land the job. Once you have some ratings and a high ranking profile, then you can start to increase your prices and be more picky about the type of work you do.
great Video to understand how to easily combine data. i tried the map with simple data on Europe sales, but Bing couldn't render more then 50% correctly, didn't even find Germany and blanked it out
Also i dont like comma as separator, cause it just hard to read of sentence of decimal numbers using comma in both variants: 1,2, 25,3, 5,12, 4,48. When it MUCH better to use DOT ONLY for decimal separator:1.2, 25.3, 5.12, 4.48. This habit gave AutoCAD. Thank you Autodesk for ordering my decimal numbers writing. :)
my Excel is not recognising the 2 date columns as dates in the CSV and in power query, in the beginning , combine and transform steps where you add column to subtract dates. This is an issue im trying to overcome often, possibly because of my regional settings? Trying to change the column data type to "date" in power query, still doesn't convert from text to a date
It'll be a locale issue. See this video for how to deal with dates that are in a different format to your locale: th-cam.com/video/QKgS3hrrmvw/w-d-xo.html
@@MyOnlineTrainingHub Thanks so much I will watch this. In the meantime I did change my regional settings in Windows to UK, and it now formats the dates in the CSV as dates (right alligned as serial numbers), so that fixed the problem for now. Enjoying your content thoroughly !! Thanks!
Do you have a video on how to set up power query? Right from the start, after selecting the two date columns, add a column, then everything in the data & time section 'grays out' and I can't select the 'date' to get to the subtract option. When I don't select any columns, date & time are available, but when I select date, the only drop down option that isn't grayed out is 'parse.'
See my reply to your previous comment. If you're still stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Wondering if the map trick will work if a new item is added. For example when a new state is added or removed from the data, do you have to manually update the ranges each time? Wouldn’t it be possible to use named ranges?
You can just extend the range being referenced to include some blank rows to allow for growth. The map chart will just ignore them until they contain data.
Thanks for the video. I use Excel, Access, and MySQL pretty regularly. In my experience setting up all the data, relationships, etc is far easier in a database (not to mention I can easily make it a standalone application). I started using Excel for a lot of the functionality that I'd typically get from a database, even though it takes far longer, just because most people don't really understand relational databases and I can make things "prettier" in Excel. Is there any other advantage for using Excel rather than just using a database where I can easily create queries, reports, etc really quickly?
If you can store your data in a database, then by all means do so. I would still use Power Query to connect to Access and bring the data into the data model/Power Pivot. The relationships you set up in Access will automatically come across, then you can create your reports in Excel, which are much nicer than Access. However, if you don't need charts, and your reports in Access are adequate, then stick with that. If you ever find yourself dealing with unstructured data, then Power Query will be a lifesaver.
I wish you would tell us how to prevent embedded workbooks (in Word documents) from mysteriously turning into un-editable pictures. MS still hasn't solved the problem, nor do they seem willing to address it.
Thank you for the effort that you made. When I apply power query in order to load and combine my Excel files, it doesn’t work as you explain in this video. I have Excel 2019. I checked the same columns and type for all my files. Maybe it works only for csv files
Dimension tables will typically come from the database you're extracting the data from, or it might be an accounting system, or sales database containing customer data, or a combination of all of the above. You'll find the sample dimension tables in the Excel file you can download from the link in the video description.
@@MyOnlineTrainingHub I downloaded the file and tried to figure out how they were generated. Was it manual? Would another video show me how you decided those tables were the ones to use?
Yes, in my file I simply have the dimension tables ready to load. I didn't generate them from anywhere as such. You decide what dimensions you need based on how you want to aggregate your data. e.g. if you want customers by state, then you'd need that information in your model and it would make sense to store that data in a dimension table, rather than the fact table.
These skills would help me to find a value of an event for every choir member if their studies yearly costs 3000 to 5000 or even up to 9000 for some students. Unknowns are current students in the event and their exact value - I may not know every faculty and their student only myself.
Not sure what you mean, but 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
Get all 3 courses in a discounted bundle from the Dashboard course page:
👩🏫 Power Query course: bit.ly/powerquery240319
👩🏫 Power Pivot & DAX course: bit.ly/powerpivot240319
👩🏫 Excel Dashboards course: bit.ly/dashboards240319
i think they became expired with the rise of power bi
@@shakiraasfoor7599 Power Bi is built upon those courses...if you understood Power Query & Dax then you can master Power BI
For anyone watching these lectures, and if you've had actual classes, you know you can't blink. So please take your time to create those pathways in your mind. Thanks for the knowledge!
😁glad you enjoyed it!
@MyOnlineTrainingHub thank you!! You're an exceptional instructor... so glad I found you. :)
Been using PQ for a couple years now and the most amazing piece was loading/cleaning and anonymising an xml source file into excel worksheet for generic viewing which took a previous incumbent 2~3 hours to complete... the PQ refresh brought it down to 7 seconds.. Absolute hero for a day😇
Woo hoo, Alan! Congratulations on that result. Power Query really is THAT amazing. I just wish more people knew.
Learning PQ was the best thing to support my work. It gooses all results to save time and frustration. I always recommend people invest in a class. Most benefit from a structured learning approach to build confidence and efficiency.
I did the same with PQ. Took a 3 - 5 hour process down to 30 minutes. Unfortunately those that came behind me try hard to break the PQ that I built. They like to add filters to the feeder data and it makes the PQ big mad!!
@@ItsjustmeAR Perhaps you can educate downstream users to make their own PQ as a reference to the one that you created that did the heavy lifting, so they can then apply their needful filtering (if that is practicable) on the data that has been expertly conditioned by you.
You are a valuable guru for Excel Learners. Wish you get a high paid from TH-cam for your precious contents...Thanks Mynda
Thanks for your support!
I discovered PQ two years ago. Thank God I did. I am controller over 73 companies. I must create a combined financial statement for all of the companies. With PQ combining the data take just a few minutes. As you can imagine, it used to take a VERY long time.
Wow, 73 companies! That's impressive and no doubt impossible to do on your own without Power Query.
@@MyOnlineTrainingHub no, it’s possible. I have done it but it takes a very long time and the patience of Job 😂
@@MyOnlineTrainingHub Your Channel Just Found Me and I am SO GLAD it did!!! I love Excel.
Love this! I've just done step one, into preparing for this phase. I have taken a monthly scorecard that was complied all manually, and took 5 days! I have now built an interactive dashboard, that just needs the data adding into the background data table worksheet, which is still currently done manually. Whilst this part only takes 15 minutes to type in, it is at risk of error, and would need a peer review to verify accuracy (additional time wasted there). So, phase 2 will be to export a CSV from there database (Ideagen), and then set up a Power Query (or, potentially export direct from the Ideagen database.
Great result! Congratulations on your success with these tools. I'm sure you're the hero of the office 😇
Wow, I have only a basic level of knowledge in Excel, but man, am I impressed. I had no idea so much could be done. I understood much of this lesson even though I could not complete the project alone. I want to learn more and become proficient in Excel. A basic class will suffice for the time being. I am very impressed with you, and now that I understand Excel, I am even more impressed with what can be done.
Glad it was helpful! You can get up to speed quickly with my courses here: www.myonlinetraininghub.com/courses
thank you for this! my goal for this year is to learn PQ & BI and basically just level up on my excel skills to save more time in terms of work. You are one of my favorite guru so far, thank you! :)
Wonderful to hear! Keep in mind that to use Power BI you also need Power Pivot skills. Good luck with it. Power BI is an amazing tool. Check out this video to get started: th-cam.com/video/gOs7EC-FebE/w-d-xo.html
Consider taking a course. It is a small dollar investment that will give you a structured learning approach to gain confidence and efficiencies.
Wow Mynda that was IMPRESIVE! Love your videos
Thanks so much!!
Pinnacle of professionalism and innovation
Thanks for watching 🙏😊
As always, you are SENSATIONAL!
Thank you!
Thanks so much, Charles!
You are awesome!! I love Excel, and your videos are exactly what I need. You speak clearly, explain the features, provide simple and examples at a steady pace. I have watched several videos and eventually stop them b/c the trainer's voice is not clear and/or boring. THANK YOU! THANK YOU! and yes, I have subscribed :) have a great day, -i
Aw, thanks so much for your kind words! It's sincerely appreciated 😊
I understood like 97% of this. I can't believe I found something I can learn and Im so fascinated by this. Like I be wanting to learn it so hard cause I never had a passion for a thing. It's achievable
That's great to hear! If you want to get up to speed quickly, please consider my courses:
👩🏫 Power Query course: bit.ly/powerquery240319
👩🏫 Power Pivot & DAX course: bit.ly/powerpivot240319
👩🏫 Excel Dashboards course: bit.ly/dashboards240319
💡Tip: Get all 3 courses in a discounted bundle from the Dashboard course page.
Excelent! Excel is a useful tool nowadays. in fact, it is a must to skill up in this era!
Excellent step-through, thanks Paul
Glad you enjoyed it, Paul!
I only care about increasing my efficiency not the company’s. Been saving so much time and giving myself back so much freedom.
Great to hear you’re reclaiming your time.
This was a very nice overview! Thank you.
Glad you enjoyed it! Thanks for watching 😊
What a great demo! Thank you so much. One thing that tripped me up for a bit was the necessitty to copy the dimension tables from your demo file into my own workbook when recreating the data model and dashboard. Going to check out your courses.
Thank you! Great to hear you gave it a go yourself. I look forward to teaching you more in my comprehensive courses.
Extraordinary!
Thanks so much 🙏
You are magician. Thanks for this tips 🎉
Glad you enjoyed my video 🙏
i am big fan of your and always follow you video, I have great respect for you and learn many things from you - you are amazing and darling, the way you teach is really superb
Thank you so much 😀
very much informative and skill improving video. Thanks
Glad you liked it!
Big Thank from Thailand.
You're welcome!
Really valuable content. Glad I watched a second time to work through the example using the sample data to really get familiar with how slick a solution this is. Thank you!
Great to hear you gave it a go!
Really impressive ,thanks for sharing the file data
🙏 glad you found it helpful 😊
Thank you. There's always a risk of automating yourself out of your job :)
You did touch on this topic but what you said is dependent on your boss thinking forward, which not all of them do.
Absolutely! Of course, with more skills comes more options for getting a new job with a boss who is forward thinking.
Mynda your tutorials are awesome magical to me....thank you so much
Mo
Thanks so much, Mo 🥰
This is just so amazing
Glad it was helpful 😊
This was great! Thank you so much!
Glad you enjoyed it! 🙏
Thanks for the video. In the state list (8:43 into the video), was wondering the need to have Oklahoma 3 times - C011, C013, and C015.
C011, C013 and C015 are different customers that all happen to live in Oklahoma. This table is used to lookup the customer number and then return the state they live in, so yes it needs to be there multiple times.
Very well presented and easy to understand.
Glad you think so! 🥰
Thanks much for detail guidance. It is a great lesson.
Glad you found it helpful!
This was such a useful and well explained video, so easy to understand 🙏
Glad you think so! 🙏
How cool is that ... watching my own comment on your video ! 😃
I can definitely attest to the time savings of using all the features that Excel can provide.
But to do this, people have to watch your channel, keep on learning and practicing! 😜
Thanks again for the superb work you're sharing.
😂 too funny! Appreciate your support 🥰
I love learning new Excel stuff and you are a good teacher. But there are a lot of business out there who aren't super ultra large corporations who would see you being able to get stuff done quicker and move to curtail your working hours because the company is spending too much money on you for the amount of things you get done. Could backfire. Still, I would rather learn so I can make decisions on my own as to how I do things, when and why etc.
I don't doubt this, but with these skills you're better positioned to find a company that values you than one who is only focused on their bottom line.
Nice tutorial, the only additional step I would do is to also name your Charts to make them easier to identify.
Cheers! Naming objects is a great idea 👍
Brilliant!! Man, I love your videos
😁 thanks so much!
Thank you!
You're welcome!
Oh Mami - yowiewowie, this is amazing - thank you for enlightening us !
My pleasure!
I like it waw , verry impressive and clearly,❤️❤️ thanx so much .
Thank you! 😃
Very helpful, thanks for sharing!
Glad it was helpful!
Always amazing!
Thank you! Cheers!
Love you & this series, Could you please make a detailed video on POWER QUERY & POWER PIVOTS, I know there are alot of resources out here but you are the best,
Support & Love!!
Great to hear! If you want to get your Power Query and Power Pivot skills up to speed quickly, I recommend you take my courses (links below) because if you rely on TH-cam, you are bound to miss important details:
www.myonlinetraininghub.com/excel-power-query-course
www.myonlinetraininghub.com/power-pivot-course
I also like your animations. You are not far from 1 M subscribers . Good luck
Thank you so much 😀
Thank you
You're welcome 😊
So much to learn as usual from your awesome contents. Thank you so much.
So nice of you 🙏😊
Thanks for the interesting video. I understand why your didn't go into using the M language to streamline your power pivot queries and spend time on producing all of the metrics used with DAX as this is a video meant to be an introduction to the basic ideas. A couple of things I think should have been included in the video is the production of a date table to enhance the performance of DateTime functions, the datamodel can even produce a basic date table automatically, I would have also liked to see fields in the main table that should be chosen from the DIM table hidden from the client as these things are pretty basic requirements for efficient use of larger datasets.
Thanks for watching and your tips, Roy! There's absolutely more to learn with these tools which I cover in detail in my courses.
Really enjoyed this video and it made me revisit my dasbboards and they are now much better. Keep up the great videos!
Wow, that's awesome to hear, Darren!
Inspiring stuff. Thanks for all your content.
Glad you enjoyed it!
my PQ analysis is now achieving 90+% success rate getting discounts for early payments where before, they may get 10%. they all think its magic. :)
Wow! Awesome to hear, Doug 👏
@@MyOnlineTrainingHub i just can't believe how many people in finance don't want to learn anything since the forward slash command from lotus 1-2-3. AI will make them redundant quick so they better get on the stick.
LOL, I remember the old forward slash commands from Lotus 1-2-3 😁
Awesome learning video 👏
Thank you 🤗
soo value video, my respect.
Glad it was helpful 😊
@@MyOnlineTrainingHub thank you
Excellent video as always!
Thanks so much 🥰
This video was not fast paced at all. Thanks Mynda.
BTW: thanks for the map chart trick - I didn’t know that one.
Good stuff, not difficult at all. Thumbs up! :-)
Great to hear, Geert! Have fun with the map charts 😊
Great video , many people underestimate the power of excel
Very true! Thanks for watching 🥰
And again it was superb.
Thanks so much!
I used to with Excel until 2016. I was of the 1% top. ... I haven't use PQ, we used to do everything with VBA.
Yeah, VBA used to be the go to tool for automation, but now it's easier to use Power Query to automate gathering and cleaning of data.
It’s a stretch to say cutting time has a direct 1:1 impact on company expenses. I agree that the one thing that people whose job is Excel heavy can do to be a rock star is focus on efficiency - not only their own but also the user of anything they produce.
It's not a stretch at all. As a consultant, I see many inefficient ways of grabbing, presenting and uploading data into accounting systems. I have developed several models that reduced time exponentially. Saved money through reduced staff time and improved accuracy.
As a cost management accountant in a previous life, it's not a stretch at all. Everything you do has a knock-on effect. With your free time you can now work on tasks/projects that haven't been tackled because you don't have the budget for extra staff. Or you can take on tasks others were doing, enabling you to save headcount. Or you can work on other tasks that generate income.
Fantastic video.
Thank you very much!
08:39. Hi Mynda. Thank you for your knowledge been share to us. Can you tell me where can I find this sheet data?
It's in the file you can download from the video desctiption.
Awesome.. thanks alot
Most welcome! 🙏😊
Excellent! I realy appreciate, Thank you 10/10
So glad you liked it! 🥰
You simply relieved my overwhelming rage to those irrelevant irritants around me, you're like a treatment of some sort of a pacifier type. Thanks ma'am. And you're a look alike of someone I remember. Just mentioning that.
Glad you enjoyed my video.
Thank you 😊
You're welcome 😊
This is so nice
Thanks so much!
Lovely❤
Thank you ☺️
Whoa!! This has increased my efficiency greatly has I can now do efficiently what too my days within 10 to 20 minutes. Thanks a lot, God blessed you. What I have another question. What about if I have multiple excel worksheet and workbook, how can I combine it into one for analysis, please can you do a tutorial about that also. Thank a lot. Waiting for your response ma.
Awesome to hear! This video covers importing data from multiple worksheets: th-cam.com/video/sLW3NbeGDy8/w-d-xo.html
Whoa!! Thanks a lot for this. God bless you.
HI! 10:11 when you connect "relationships" make connections between data sets, what is the purpose of connecting the data sets? is this effectively creating the data model? :/
Technically, the data model can be a single table. The relationships between the tables enable you to filter your data based on fields in the related tables. For example, we can summarise the sales by the Category field in the CategoryDim table. You couldn't do this without the relationship between those two tables because the 'Categfory' field doesn't exist in the SalesData table.
Great instruction! Can you explain why the sales map chart is so dim at the very end? Looks like an error, but I think I’m just missing something
The map chart uses a color scale with the lower values in the paler color. You can change the color scale to use a different color for lower values rather than paler shades of the same color.
🥰🥰 Perfect
Thanks so much 🥰
Thank you so much for the video, it is really amazing. I have one issue after cleanning the data in power quary, I incorrectly chose close and upload to power pivot, I go back to power quary, but after that I dont have " Close and Load to" , it is gray, and I only have " Close and Load". How can I find the " Close and load to " functions?
Thank you.
Great to hear and well done for trying it yourself. You can access Close & Load To from the Queries and connections pane (Data tab of the ribbon) > right-click the query you want to edit the load settings for.
Can you please tell me how to select multiple on slicers or clear selection once you remove headers?
Hold SHIFT to select multiple contiguous buttons, hold CTRL to select non-contiguous buttons. Clear selection by selecting first button > hold SHIFT > select last button.
As a newbie wanting to learn excel 365 by buying microsoft 365 subscription, where do i start? Can u pls link me to a beginner video and intermediate. Thank u
You can get up and running with my free courses: www.myonlinetraininghub.com/free-registration
hi thanks for the content just one question, in the 9.05 where do i get that second table in blue ? as i dont have it in the extracted file thanks
The tables are in the Excel file included in the download.
In powerpivot 08:02 my order date and ship date columns are empty. Previously I changed their datatype to locale date.
Sounds like maybe you didn't remove the previous 'changed type' step before changing their type using the locale option.
For visualisation (your third suggestion), is it better/alternative POWER BI?
An alternative is Power BI, but keep in mind that not all companies have the resources and budget for Power BI, so also knowing data visualisation in Excel is beneficial.
Very Very Very Goooooood
Thanks so much!
Heroo🔥🔥🔥😍
Glad you liked it 🙏😊
thanks
You're welcome!
Thank you Mynda - I found your content a few years ago and everything I have seen is top notch. I've just scratched the surface with power query, experimenting with some public data sets. Where I get stuck is inconsistencies with how source data was entered, for example information that should be multiple columns becomes grouped into a single without rhyme or reason. This makes splitting columns ineffective. Is this a topic addressed in your course?
Great to hear you've found my videos helpful! I cover splitting columns in my Power Query course, yes. And if your specific scenario isn't covered, we provide support to our students so you can reach out and get help.
Hi, I love the Power Query overview. Now, I'm trying to do the same with real data and trying to connect my org chart to tie into my nps queries and I am missing a step or don't know how to incorporate my org chart into my original query.
Hard to help without knowing how your data is structured. 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
Hi, excellent video, but could you explain how did ou get to have the blue boxes? Cannot find it out where did you click to obtain the data:)
The data in the blue tables is in the Excel file you can download from the file in the video description. In that file, they're on the "Dimension Tables" sheet.
Great Content! But there is a mistake in the calculation shown in the Days to Ship column, when you used Power Query. The difference between 01/01/2021 and 03/01/2021 is 59 days. Not 2 days
My dates are dd/mm/yyyy 😉
15:45 i dont understand why we cant?
I know how to do everything in this video. What positions/roles should I be searching for on LinkedIn, Indeed, or Upwork?
Have had no luck looking for analyst positions on Indeed and LinkedIn for months now...
The key with these platforms is to build up a profile and ratings so that you have some credibility. As a result, I would apply for anything you think you can do, and price yourself very competitively to land the job. Once you have some ratings and a high ranking profile, then you can start to increase your prices and be more picky about the type of work you do.
3:52 i dont like step "Transform file from example", so I always try to avoid it with conversion of files in additional column.
That's the old way of combining files, and works just as well for the most part.
great Video to understand how to easily combine data. i tried the map with simple data on Europe sales, but Bing couldn't render more then 50% correctly, didn't even find Germany and blanked it out
The map charts are a bit hit and miss. Need to label the columns in a particular way for it to play nice.
Also i dont like comma as separator, cause it just hard to read of sentence of decimal numbers using comma in both variants: 1,2, 25,3, 5,12, 4,48. When it MUCH better to use DOT ONLY for decimal separator:1.2, 25.3, 5.12, 4.48. This habit gave AutoCAD. Thank you Autodesk for ordering my decimal numbers writing. :)
If you're in a region where the dot isn't used for decimal places then that's ok, but I wouldn't recommend it otherwise.
This is awesome. What would happen if the 2024 file added a new salesperson or lost one? What about a changed category name in the 2024 file?
You can make those changes and everything will update accordingly without having to edit the query or report.
Amazing
Thank you! Cheers!
my Excel is not recognising the 2 date columns as dates in the CSV and in power query, in the beginning , combine and transform steps where you add column to subtract dates. This is an issue im trying to overcome often, possibly because of my regional settings? Trying to change the column data type to "date" in power query, still doesn't convert from text to a date
It'll be a locale issue. See this video for how to deal with dates that are in a different format to your locale: th-cam.com/video/QKgS3hrrmvw/w-d-xo.html
@@MyOnlineTrainingHub Thanks so much I will watch this. In the meantime I did change my regional settings in Windows to UK, and it now formats the dates in the CSV as dates (right alligned as serial numbers), so that fixed the problem for now. Enjoying your content thoroughly !! Thanks!
Nice advice for a diplomatic woman. I the Caesar King shall prolong a bit my patience and restore and activate the systems deactivated. Let it be
can you use power query to choose which products are good for new marketplace expansion?
Power Query is a data gathering and cleaning tool. You’d have to then analyse the data, which you could use Power Pivot for.
@@MyOnlineTrainingHub thank you
Do you have a video on how to set up power query? Right from the start, after selecting the two date columns, add a column, then everything in the data & time section 'grays out' and I can't select the 'date' to get to the subtract option. When I don't select any columns, date & time are available, but when I select date, the only drop down option that isn't grayed out is 'parse.'
See my reply to your previous comment. If you're still stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Wondering if the map trick will work if a new item is added. For example when a new state is added or removed from the data, do you have to manually update the ranges each time? Wouldn’t it be possible to use named ranges?
You can just extend the range being referenced to include some blank rows to allow for growth. The map chart will just ignore them until they contain data.
@@MyOnlineTrainingHubis it not possible to reference by table column?
Thanks for the video. I use Excel, Access, and MySQL pretty regularly. In my experience setting up all the data, relationships, etc is far easier in a database (not to mention I can easily make it a standalone application). I started using Excel for a lot of the functionality that I'd typically get from a database, even though it takes far longer, just because most people don't really understand relational databases and I can make things "prettier" in Excel. Is there any other advantage for using Excel rather than just using a database where I can easily create queries, reports, etc really quickly?
If you can store your data in a database, then by all means do so. I would still use Power Query to connect to Access and bring the data into the data model/Power Pivot. The relationships you set up in Access will automatically come across, then you can create your reports in Excel, which are much nicer than Access. However, if you don't need charts, and your reports in Access are adequate, then stick with that.
If you ever find yourself dealing with unstructured data, then Power Query will be a lifesaver.
I wish you would tell us how to prevent embedded workbooks (in Word documents) from mysteriously turning into un-editable pictures. MS still hasn't solved the problem, nor do they seem willing to address it.
Thank you for the effort that you made. When I apply power query in order to load and combine my Excel files, it doesn’t work as you explain in this video. I have Excel 2019. I checked the same columns and type for all my files. Maybe it works only for csv files
It should work the same for Excel files. In the Excel file, the table/sheet names must be the same in each file.
@ Thank you I will try.
How were the lookup tables created?
Dimension tables will typically come from the database you're extracting the data from, or it might be an accounting system, or sales database containing customer data, or a combination of all of the above. You'll find the sample dimension tables in the Excel file you can download from the link in the video description.
@@MyOnlineTrainingHub I downloaded the file and tried to figure out how they were generated. Was it manual? Would another video show me how you decided those tables were the ones to use?
Yes, in my file I simply have the dimension tables ready to load. I didn't generate them from anywhere as such. You decide what dimensions you need based on how you want to aggregate your data. e.g. if you want customers by state, then you'd need that information in your model and it would make sense to store that data in a dimension table, rather than the fact table.
These skills would help me to find a value of an event for every choir member if their studies yearly costs 3000 to 5000 or even up to 9000 for some students. Unknowns are current students in the event and their exact value - I may not know every faculty and their student only myself.
Not sure what you mean, but 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 I meant eu parliament