Power query for me but I guess that since there are so many new functions in Excel, it’s difficult for even seasoned users to know where to begin with complex problems.
@@Michael-sn1wq Many Excel users struggle with it. I have a playlist dedicated to Power Query, I hope it help: th-cam.com/video/wk1s0wE5Gww/w-d-xo.html&pp=iAQB 😊
LET function .... I have used and trained Excel for years but for some reason I steered clear of this function, perhaps that is why I have never understood it and known what is it for. Is it something really needed? Thank you Mynda for fantastic videos, Gregory (PS. Just noticed someone already mentioned this function. And there is a video on it. So looks like one of those days I need to break ice with this strange function)
Wow!!! This is wonderful video, Mynda!!!! The reason it is wonderful is because you tell the truth about data analysis: you often do not need tools like R and Python. Excel is right there in front of you ready to sever : ) Then you go on and show a great suite of Excel tools and even showed the Data Analysis Descriptive Statistics tool!
I love this type of videos. It's not just step by step tutorial, but more a worflow with best practices during which you can learn multiple things by seeing them in real usecases.
Thanks for the suggestion and your support! 🥰 please check out my video on LET: th-cam.com/video/o3ysGnl8ieM/w-d-xo.html And my video on LAMBDA: th-cam.com/video/cGxjWOY8h98/w-d-xo.html
Brilliant thank you. You asked what everyone would like more of, I personally would like more on automation. I'm a Procurement Manager and therefore work with a load of numbers but not in an accounting setting. Bid comparisons, analysis of those, tracking invoice payments against contracts, indices etc. it would be great to help me simplify my work. I've already incorporate a lot of your suggestions, but I can always do better. Thanks
This may seem silly. But I struggle with modifying pivot table formats. I try to create from an existing layout, but they never come out the way I want. I end up using formatting using format/cells.
There is no such thing as a silly question :) Struggling with PivotTable formats is a common issue. PivotTables often use default formatting, which might not align with your needs. Here are some quick tips: use 'Field Settings' to adjust the layout and format for each field, explore 'PivotTable Styles' in the Design tab for predefined styles, and ensure 'Preserve cell formatting on update' is checked in PivotTable options. This will maintain your formatting even after refreshing the data. Consider changing the report layout under the Design tab for a more customized approach. Hope this helps!
And for big data, that require more than the limit rows excel offers (1,048,576) what will a solution to data analytics? I'm so used to do everything in excel, but I have a special task to do that involves at least 5 files with that limitation of rows.
You can still use Excel. See this video which uses Power Query and Power Pivot which can handle 100s of millions of rows of data: th-cam.com/video/McLQ3gEGbHQ/w-d-xo.html
Thank you Mynda. Regarding your question, yes I have the feature of (=SEQUENCE(1,141,MIN(C9:C58)+Work!K5,1)) what if I want to show the project baseline on weekly basis?
Hi Mynda, the topic I'm more interested in is creating an Excel Dashboard TEMPLATE. I work with the same data download format for housing markets say 6 different times per week, say 6 different jobs. I want to build a template of say 8 key metrics I need from my data. After the template is built, I would use it to add different data sets several times per week.
That's a great idea! You may also be able to use Power Query to automate gathering the data, then you can just point Power Query to a different file for each housing market. If you'd like to learn how to build dashboards, please consider my Excel Dashboards + Power Query course bundle available here: www.myonlinetraininghub.com/excel-dashboard-course
Excellent! Topic which interests me is: Can Excel now replace Access for smaller databases? Two main functions which Access has served: 1. Long-term reliable data storage; 2. Error checking of the data.
Great question! In theory, you can use Excel as a database, but it's not as robust as a proper database. You can use Power Pivot to model the data across multiple tables with relationships to connect them, like you can in access. And you can use Power Query to perform error checking and data cleaning tasks.
Thank you for the video, you're simply brilliant! I would be happy to learn more about statistical tools in Excel and also about various uses of the SEQUENCE formula.
Thank you Mynda. Regarding your question: I'd like to learn more about power pivot. Would be great to see a video in the near future. Thanks in advance!
Ah, that's a shame. Don't give up on it. One day it will click for you. Here's my 4 minute video that might help: th-cam.com/video/-4yCXpv-drg/w-d-xo.html
Excellent video! Goes pretty fast though. My biggest challenge with using Power Query on rent rolls and some other files is that the data is pulling typically from PDF reports. All rows have data for a unit, but for each unit there are multiple pieces of data (for multiple portions of rent collected from a tenant - like pet fee, rent, fireplace fee, etc. But some columns of data word wrap (like the tenant name), so I can't figure out how to "unwrap" those columns of data so they don't appear as separate rows of data. I have the same issue when pulling operating statements where the name of an account word wraps on the report and I don't know how to set those to a single row.
Hi videos are always useful. Thank you so much. My query is related to using power pivot. 1. I struggle with building relationships 2. To combine tables as you have done in this video, do all your separate tables have to have same fields etc for them to work in the combining?
Thank you! To answer your second question, the first file (sample file) has to contain all the columns your other files could possibly contain, but the other files don't have to contain all the same fields. Note: this combining of files in the video was done in Power Query, not Power Pivot. Building relationships in Power Pivot can be tricky and it's such a critical step in ensuring your model works correctly that it might be worth you taking a Power Pivot course to learn the fundamentals: www.myonlinetraininghub.com/power-pivot-course
Wow, it is a fascinating new world. I feel a little bit overwhelmed since I feel like I would like to understand it all. I will start step by step and purchase the Dashboard course now that has a discount. And then look for more topics. Thanks for opening my eyes Mynda.
So pleased it was helpful. There is a lot to Excel, but learning something new each week will be super rewarding. Excel skills will never go to waste 😁
Hello Mynda, I am in desperate need of conditional formatting direction - I need to Highlight Mismatched dates between 3 columns - helP! it's driving me around the bend 😜
Hello, I am a financial advisor and I manage my clients' money. I need two dashboards: - One to track my prospects and view sales metrics. - One to report to my clients about their assets (investments and goals). Which one would you recommend? I appreciate your contributions, they are incredibly valuable. Thank you
I'm not sure why you're asking me which one I recommend when it seems you need both as they serve different purposes. If you'd like to learn how to build your own interactive dashboards, please consider my course: www.myonlinetraininghub.com/excel-dashboard-course
Amazing! Thanks. I like : pivots and Data Analysis tools and I'm eager to know about Power Pivot. Question thought : is 'Office Pro Plus 2021' able to provide this IA Data Analysis tool? Thanks a lot
Hello, I've building a pivot tabel of my company's balance. It's works fine except for the fact that i don't know how to handle the 5 level i've used for classify all the balance voices. I'd like ask you if exist a tecnique to avoid in a pivot tabel to see empty level, of course i would like to don't use +/-. thanks in advance
Difficult to say without seeing the file and structure of your data. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I would love to learn how to perform row-dependent calculations in powerquery. Use case: every month I download a csv file of my electricity consumption which provides a snapshot of the meters in 15 minute increments. So when I sort my data in powerquery and I append a month's worth of data each time. I want to deduct row 2 from row 1, on row 2, to give me the electricity consumption in that 15 min window. I now run this in the data table in my workbook, but looking for ways to do this in powerquery instead.
Try this. Add two Index columns named Index 1 and Index 2 with Index 1 starting from 0 and Index 2 starting from 1. Then create a new merge query doing a self join where you connect on Index 1 in the first instance of the table and Index 2 in the second instance. Go with the Left Outer Join option. From there expand the new column that will contain a table in each row. You'll probably have to sort your date field again. Then do a subtraction transformation as a new column (e,g. "Consumption" - "Consumption.1"). This will give you the difference for each time interval. Then delete the columns you don't need and rename the subtraction column.
Thanks for the suggestion. I have found arrays much easier to understand since dynamic array formulas were introduced: www.myonlinetraininghub.com/excel-dynamic-arrays
Thanks for the video! Always very useful! However I've experienced some issue regarding "Analysis ToolPak". It seems activated but no button was shown on the ribbon. I tried to find what's the problem on the internet but without success...
@@MyOnlineTrainingHub I even tried to logoff and login from my company account but nothing. Very sad about this 😢. I tried to use “Analyze data” (as explained in your video) and looks very cool! But I think that this add-in seems to have more functionalities!
Thanks for sharing. DAX is one of those things that requires practice. If you'd like to get your skills up to speed quickly, please consider my Power Pivot and DAX course: www.myonlinetraininghub.com/power-pivot-course
Dear Madam, Thank you very much for the "ANOTHER GREAT VIDEO". I have a request, how to creat an excel formula, the inside of an excel formula contains, arrays, conditions, etc etc so while creating the formula how to understand that elements and putting them correctly, I hope you got my point. I will be very grateful if you make it easy for us.
Great video as usual . I believe when we upload files from a folder in power query and select the first file for sample query, if the position of the the file changes in the folder , it effects the query, right? Is there anyway to make it dynamic ? Other than selecting a specific file for sample query? Well, about an excel function that I wanted to learn better is OFFSET, 😊 thank you
Glad you liked it 🙏 Good point about the position of the file in the folder changing. You could make it dynamic with a modification to the functions to pick up a parameter instead. For OFFSET, please see this video: th-cam.com/video/sRE9jAMqF7A/w-d-xo.html
@@MyOnlineTrainingHub Thank you for the response:) I have watched the OFFSET tutorials, it’s really simple and that’s what actually I wanted, starting from a basic use to a complex!! And , I don’t know much about using Parameters in power query, if you have done a tutorial already, pls let me know. I have a question about combining files from folder, first row of all my files are header, so when I promotes first row as header , it doesn’t applying to the rest of the files, they remain in data and I have to filter to remove them. Is it common? Appreciated your reply. Thank you 🙏
Good stuff "mate", thanks again! I would like to know more about power query and then specifically how to handle data from multiple sources which contain different deliminators and thousands/decimal settings. For example, masterdata is loaded from an Excel file (with US/English settings on laptop) and the sales table from a CSV (produced from a Dutch database). I somehow always struggle to get this right.
Thanks for watching! Each of your queries would have to be created separately and cleaned before appending/merging them (assuming that's the end goal). I'm not sure there's an easy way to help you remember the steps as such, but once you set up the queries, you should be able to reuse them for future files.
@@MyOnlineTrainingHubThanks for getting back! I just found the "using locale" option, in the "change type" submenu. And it seems I can deviate from the system settings like this.
Hi Mynda, could you share a link in your videos where I can find out how to add funds to my stocks portfolio account, I am uk based and use mainly uk stocks but now I have a couple of funds us as as India and uk but I can’t seem to find the correct way to add them to my excell I have the correct ticker but I cant find how to add them in, as excel wants to give me other tickers 😂 Any help would be greatly appreciated thank you Kind regards kev
Ok thank you for your very quick reply.. For example i put theses 2 in like this into spreadsheet HSBC FTSE 100 INDEX INCOME C, but the reply i get is this HSBC HOLDINGS PLC (XNYS:HSBC) and doesn't see anything else Vanguard U.S. Eq Idx £ Acc, and i actually hold these two in my account. so i know they exist 🤣 @@MyOnlineTrainingHub
There are now over 1M rows available in the grid (a lot more than in the 90s). However, if 1M is still not enough, you can use Power Query to load your data into the Power Pivot data model where you have unlimited rows of data you can work with. From there, you can build PivotTables to summarise and analyse the data. If you'd like to see these tools in action, check out this video: th-cam.com/video/TK0CJBaqvnY/w-d-xo.html
When I was working, I did a kind of recertification using Excel, and the data was massive. Although I had a few days after training to go about it, I was not familiar with it. Do you know what that could be?
I hear you! 😁 check out my LAMBDA video here: th-cam.com/video/cGxjWOY8h98/w-d-xo.html
7 หลายเดือนก่อน
I have not figured out how to work with excel in a good way for statistical analysis. Lets say I have 10-20000 rows of data from four pumps in a factory. I plot a regression that shows there are some outlier points. How do I exclude these points moving forward in the statistical analysis (before I make more calculations or the regression analysis etc.) ? In the plot I can see the x,y of the points, but going into the table with 10000+ rows it is a lot to scroll down and find the points and delete them. Using filter on the column dosnt work either as there are data for the other pumps in the same row that is not outliers. In R/Python there are some options forexample making a new df excluding these points. But I dont an Excel equavalent to this way without ending up with alot of individual tables. What if it is not outliers I want to remove, but only make the an regression analysis on a section of data and another analysis on another part.
You could possibly use the FILTER function to exclude the outliers and create a subset of your data for further analysis. FILTER function is covered here: th-cam.com/video/ZCQAweoAdOw/w-d-xo.html
I wish I understood better how I could automatize the integration of csv file and split concatenated values from one cell in dedicated Column with corresponding headers e.g in one cell (Length [mm] : 20; Height [mm] . 12; Depth [mm] : 5) tranform to columns with Header Column 1 : Length [mm] : each row value corresponding length e.g. 20 Header Column 2 : Height [mm] ....
Sounds like a job for Power Query. If you get stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
They are for me. What makes you say they're not? Follow the link in the video description, enter your email address to reveal the download button and then download the file.
Sorry , can you tell again who can have the ANALYZE DATA feature?Thanks - I cannot work it out how to add it...365office account and also personal 365 version license.
My issue is using Power Query to clean up data generated by badly designed reports. I have tried to use Power Query but can't seem to get data into the proper rows and columns.
Thanks for sharing! In future 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
HI there. first of all thank you for your intelligent videos , second : i would like to ask for your permission to use the data you used in the budget vs actual vs plan video ( I dont want the dashboard i just need the data sheet as it would help me in designing sample data for my clients . )
@@MyOnlineTrainingHub sorry but is designing a budget file from my own with this data and put it on upwork as a portfolio would be considered commercial ?
I wish I could learn VBA. I want to learn it so bad. I dont know where to start. I am Chartered Accountant and proficient in excel but VBA is the missing piece.
What do you want to use VBA for? Many times VBA is not required and it's even less useful now that we have Power Query to automate the gathering and cleaning of data.
You are looking Beautiful. Thanks for the video. can you please make video on Substitute function in excel (Advance level).that i wish to understood better.
Power Query is, indeed, a feature that many Excel users find hard to work with. I have a playlist dedicated just to it: th-cam.com/video/wk1s0wE5Gww/w-d-xo.html&pp=iAQB I hope you will find the answers to your questions there 😊
Oh man! Where was this video 13 years ago? 😉 To answer your question, it would be macros. BUT, from what i know and do, I've just stopped using macros because they "break" all the time, require maintenance, and can destroy the sheet, etc. 😎
Hopefully this video will help you get started with that.
2 หลายเดือนก่อน
I enjoy watching your videos. I would appreciate seeing more videos that relate to workforce data to show staff numbers, gender, tenure, hours worked, CALD, ect. This would be so helpful. Thanks.
Hi, why do you use the average? Do you want to calculate an average of % GP (what meaning?) or % GP per country? this is not the REAL %GP!! Indicators (measures) such as % margin, % Discount, Price ... never calculated in ETL process.
Good questions. The inputs are up to you. The purpose of the video is to show you how you can use Excel to perform common calculations that you might ordinarily do in a more convoluted way.
❓What is one Excel feature or function that you wish you understood better?
Improve your productivity in Excel with Power Query: bit.ly/powerquery0124
Power query for me but I guess that since there are so many new functions in Excel, it’s difficult for even seasoned users to know where to begin with complex problems.
@@Michael-sn1wq Many Excel users struggle with it. I have a playlist dedicated to Power Query, I hope it help: th-cam.com/video/wk1s0wE5Gww/w-d-xo.html&pp=iAQB 😊
Pivot Table and Mynda Treacy😎😎🌺🌺
There is a module for Data Profiling ?
LET function .... I have used and trained Excel for years but for some reason I steered clear of this function, perhaps that is why I have never understood it and known what is it for. Is it something really needed? Thank you Mynda for fantastic videos, Gregory
(PS. Just noticed someone already mentioned this function. And there is a video on it. So looks like one of those days I need to break ice with this strange function)
100% my most favourite Excel instructor. Everything is done so well and clearly. Thank you and bless you always!
Wow, thank you! 🥰
Wow!!! This is wonderful video, Mynda!!!! The reason it is wonderful is because you tell the truth about data analysis: you often do not need tools like R and Python. Excel is right there in front of you ready to sever : ) Then you go on and show a great suite of Excel tools and even showed the Data Analysis Descriptive Statistics tool!
Thanks so much, Mike!
@@MyOnlineTrainingHub Really Good : )
I love this type of videos. It's not just step by step tutorial, but more a worflow with best practices during which you can learn multiple things by seeing them in real usecases.
Awesome to hear 😁🙏
excellent.....i never get bored with your tutorials. Love it
Mo
Glad you like them!
Let and lambda helper functions. Thank you for all you do to help people become more proficient in Excel. Love all your videos.
Thanks for the suggestion and your support! 🥰 please check out my video on LET: th-cam.com/video/o3ysGnl8ieM/w-d-xo.html
And my video on LAMBDA: th-cam.com/video/cGxjWOY8h98/w-d-xo.html
Brilliant thank you. You asked what everyone would like more of, I personally would like more on automation. I'm a Procurement Manager and therefore work with a load of numbers but not in an accounting setting. Bid comparisons, analysis of those, tracking invoice payments against contracts, indices etc. it would be great to help me simplify my work. I've already incorporate a lot of your suggestions, but I can always do better. Thanks
Interesting 🙏 will keep it in mind.
Mynda you are the best! I am checking your news as often as weather forecast.
😁thanks so much!
Macros is the Excel feature I wish I know more about, for some reason I don't see a lot of people talking about it
Thanks for sharing! Macros are not as important as they once were now that we have Power Query to automate the gathering and cleaning of data.
Kindly advise how should I master the art of reconciliation as accountant both bank and vendors accounts. Thanks
Really fascinating ; you made it really simply and believable.
Great to hear 🙏
This may seem silly. But I struggle with modifying pivot table formats. I try to create from an existing layout, but they never come out the way I want. I end up using formatting using format/cells.
There is no such thing as a silly question :) Struggling with PivotTable formats is a common issue. PivotTables often use default formatting, which might not align with your needs. Here are some quick tips: use 'Field Settings' to adjust the layout and format for each field, explore 'PivotTable Styles' in the Design tab for predefined styles, and ensure 'Preserve cell formatting on update' is checked in PivotTable options. This will maintain your formatting even after refreshing the data. Consider changing the report layout under the Design tab for a more customized approach. Hope this helps!
Anything you could include regarding regular expressions would be good
Thanks. Will keep that in mind.
And for big data, that require more than the limit rows excel offers (1,048,576) what will a solution to data analytics? I'm so used to do everything in excel, but I have a special task to do that involves at least 5 files with that limitation of rows.
You can still use Excel. See this video which uses Power Query and Power Pivot which can handle 100s of millions of rows of data: th-cam.com/video/McLQ3gEGbHQ/w-d-xo.html
Could you do a video about how to use scenario's?
Thank you Mynda. Regarding your question, yes I have the feature of (=SEQUENCE(1,141,MIN(C9:C58)+Work!K5,1)) what if I want to show the project baseline on weekly basis?
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
what excel function do i need to improve on? If Then functions. I get confused on the syntax, when to add a parenthesis, etc. 😟
Check out this video for IF statements: All 3 Excel IF Statements + how to AVOID PERFORMANCE ISSUES!
th-cam.com/video/0zTeXujtOZI/w-d-xo.html
@@MyOnlineTrainingHub thank you!
Hi Mynda, the topic I'm more interested in is creating an Excel Dashboard TEMPLATE. I work with the same data download format for housing markets say 6 different times per week, say 6 different jobs. I want to build a template of say 8 key metrics I need from my data. After the template is built, I would use it to add different data sets several times per week.
That's a great idea! You may also be able to use Power Query to automate gathering the data, then you can just point Power Query to a different file for each housing market. If you'd like to learn how to build dashboards, please consider my Excel Dashboards + Power Query course bundle available here: www.myonlinetraininghub.com/excel-dashboard-course
Thank you! @@MyOnlineTrainingHub
7:03, I understand why you put the discount% in the values section of the pivot table, but why put the discount% in the rows section?
By putting the discount also in the row labels I can group them into bins.
Excellent!
Topic which interests me is: Can Excel now replace Access for smaller databases?
Two main functions which Access has served:
1. Long-term reliable data storage;
2. Error checking of the data.
Great question! In theory, you can use Excel as a database, but it's not as robust as a proper database. You can use Power Pivot to model the data across multiple tables with relationships to connect them, like you can in access. And you can use Power Query to perform error checking and data cleaning tasks.
Thank you for the video, you're simply brilliant! I would be happy to learn more about statistical tools in Excel and also about various uses of the SEQUENCE formula.
Glad you enjoyed it. Thanks for the suggestion 🙏
Frankly, since the day i follow you. i found excel is very easy to use and no function is hard to learn. Thank you!
Aw, you’re too kind 🙏
@@MyOnlineTrainingHub seriously no unwanted background music, simple and decent way of teaching. Thank you very much!
Thank you Mynda. Regarding your question: I'd like to learn more about power pivot. Would be great to see a video in the near future. Thanks in advance!
Thanks for sharing. If you'd like to get up to speed with Power Pivot & DAX, check out my course here: www.myonlinetraininghub.com/power-pivot-course
Hi Mynda. I always have trouble with Index and Match to the point I will avoid it all costs if possible.
Ah, that's a shame. Don't give up on it. One day it will click for you. Here's my 4 minute video that might help: th-cam.com/video/-4yCXpv-drg/w-d-xo.html
Excellent video! Goes pretty fast though. My biggest challenge with using Power Query on rent rolls and some other files is that the data is pulling typically from PDF reports. All rows have data for a unit, but for each unit there are multiple pieces of data (for multiple portions of rent collected from a tenant - like pet fee, rent, fireplace fee, etc. But some columns of data word wrap (like the tenant name), so I can't figure out how to "unwrap" those columns of data so they don't appear as separate rows of data. I have the same issue when pulling operating statements where the name of an account word wraps on the report and I don't know how to set those to a single row.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi videos are always useful. Thank you so much. My query is related to using power pivot.
1. I struggle with building relationships
2. To combine tables as you have done in this video, do all your separate tables have to have same fields etc for them to work in the combining?
Thank you! To answer your second question, the first file (sample file) has to contain all the columns your other files could possibly contain, but the other files don't have to contain all the same fields. Note: this combining of files in the video was done in Power Query, not Power Pivot.
Building relationships in Power Pivot can be tricky and it's such a critical step in ensuring your model works correctly that it might be worth you taking a Power Pivot course to learn the fundamentals: www.myonlinetraininghub.com/power-pivot-course
I loooove this kind of stuff. I didn't know you can do this in Excel!
So glad it was helpful 😊
Hi, is there any formula to calculate the no of fractional months b/w two dates.
Both start and end date should be included.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi, thanks.. I will post my query in forum with sample excel file.
Good Morning Mynda and thank you for all you do!
Thank you for the support! I really appreciate that 😊
Wow, it is a fascinating new world. I feel a little bit overwhelmed since I feel like I would like to understand it all. I will start step by step and purchase the Dashboard course now that has a discount. And then look for more topics. Thanks for opening my eyes Mynda.
So pleased it was helpful. There is a lot to Excel, but learning something new each week will be super rewarding. Excel skills will never go to waste 😁
@@MyOnlineTrainingHub Agree 100 %. I started already with the dashboard course amazing, I also purchased the Power Query one…
Excellent Video. Thank you Mynda.
You are most welcome!
Hello Mynda, I am in desperate need of conditional formatting direction - I need to Highlight Mismatched dates between 3 columns - helP! it's driving me around the bend 😜
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Keep Pressing. You are Dynamite!!
Thank you, I will 🙏😁
Dax functions and how to apply then to real cases. thank you. great video!
Thanks for watching! Will keep DAX in mind. 👍
Thank you for sharing!
My pleasure!
I really want to understand power query better ! Thank you for the video this is what i need ! ❤
Great to hear! Please check out my Power Query course to get your skills up to speed quickly: www.myonlinetraininghub.com/excel-power-query-course
Thank you so much for the video
My pleasure!
Welcome back, and thank you for sharing this.
Thanks for watching 😊
Hello, I am a financial advisor and I manage my clients' money.
I need two dashboards:
- One to track my prospects and view sales metrics.
- One to report to my clients about their assets (investments and goals).
Which one would you recommend? I appreciate your contributions, they are incredibly valuable.
Thank you
I'm not sure why you're asking me which one I recommend when it seems you need both as they serve different purposes. If you'd like to learn how to build your own interactive dashboards, please consider my course: www.myonlinetraininghub.com/excel-dashboard-course
Many thanks for asking. I would like to learn relationship for Data Model in excel
Thanks for sharing. Will keep it in mind.
Amazing! Thanks.
I like : pivots and Data Analysis tools and I'm eager to know about Power Pivot.
Question thought : is 'Office Pro Plus 2021' able to provide this IA Data Analysis tool?
Thanks a lot
Glad you liked it. The Analyze Data tool is only available in Excel 365, sorry.
Thanks a lot, love it!!!
So great to hear 🙏
Wonderful 🌹
Thanks for watching!
Perfect information thanks a lot 🥰🥰🥰
Glad it was helpful! 😁
Excellent! Thank you Mynda! I vote for Power Query!
Thank you 😊
Hello, I've building a pivot tabel of my company's balance. It's works fine except for the fact that i don't know how to handle the 5 level i've used for classify all the balance voices. I'd like ask you if exist a tecnique to avoid in a pivot tabel to see empty level, of course i would like to don't use +/-. thanks in advance
Difficult to say without seeing the file and structure of your data. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi mam could you also give suggestion how to link excel spread sheet from share point to qr code
Just get the SharePoint 'share' link for the Excel file and create a QR code for it.
Thank you Mam you are better than the best.power query playlist is dream video series.
So nice of you 🥰
😀😀😀Great tutorial thank you!
Glad it was helpful! 😊🙏
Excellent Mynda!!
Thank you for watching and for the comment!
I would love to learn how to perform row-dependent calculations in powerquery.
Use case: every month I download a csv file of my electricity consumption which provides a snapshot of the meters in 15 minute increments. So when I sort my data in powerquery and I append a month's worth of data each time. I want to deduct row 2 from row 1, on row 2, to give me the electricity consumption in that 15 min window. I now run this in the data table in my workbook, but looking for ways to do this in powerquery instead.
Try this. Add two Index columns named Index 1 and Index 2 with Index 1 starting from 0 and Index 2 starting from 1. Then create a new merge query doing a self join where you connect on Index 1 in the first instance of the table and Index 2 in the second instance. Go with the Left Outer Join option.
From there expand the new column that will contain a table in each row. You'll probably have to sort your date field again. Then do a subtraction transformation as a new column (e,g. "Consumption" - "Consumption.1"). This will give you the difference for each time interval. Then delete the columns you don't need and rename the subtraction column.
@@brianxyz 😁 thank you!!! this is great, I just tried it out and it works to my expectation. Saturday night hobby :)
Excel's Array concept? How it works, when we will use it in forumals .
Thanks for the suggestion. I have found arrays much easier to understand since dynamic array formulas were introduced: www.myonlinetraininghub.com/excel-dynamic-arrays
Thanks for the video! Always very useful! However I've experienced some issue regarding "Analysis ToolPak". It seems activated but no button was shown on the ribbon. I tried to find what's the problem on the internet but without success...
Hmm, not sure why you can't see the button. Try rebooting your PC/restarting Excel.
@@MyOnlineTrainingHub I even tried to logoff and login from my company account but nothing. Very sad about this 😢.
I tried to use “Analyze data” (as explained in your video) and looks very cool! But I think that this add-in seems to have more functionalities!
Thank you for your amazing learning videos. Lambda for me is difficult to understand and find it usefull.
You are welcome! Here is my LAMBDA tutorial: www.myonlinetraininghub.com/excel-lambda-function Hope it helps.
You asked what is one thing we wish we understood better. For me, that would be using DAX with Excel (e.g. measures)
Thanks for sharing. DAX is one of those things that requires practice. If you'd like to get your skills up to speed quickly, please consider my Power Pivot and DAX course: www.myonlinetraininghub.com/power-pivot-course
@@MyOnlineTrainingHub thank you very much for the suggestion. I will follow your advice. Cheers!
To think that I was doing this manually last night 😢😢...Thank you for this 🙏🙏🙏
Yay, so glad I could save you some time going forward!
index and match
Dear Madam, Thank you very much for the "ANOTHER GREAT VIDEO". I have a request, how to creat an excel formula, the inside of an excel formula contains, arrays, conditions, etc etc so while creating the formula how to understand that elements and putting them correctly, I hope you got my point. I will be very grateful if you make it easy for us.
Great suggestion! 🙏
Thank you Mynda.
Thank you for watching and for the comment 😊
IHi nterested in preparing data for dynamic dashboard step-by-step guide. Thanks
Awesome. Check out this dashboard playlist: th-cam.com/play/PLmd91OWgLVSLy87GfZCR74BG1nhO7ER0s.html
I wish if i could understand DAX better. Please do a video series in this.
DAX is a great skill to master, but it takes practice. I have a Power Pivot & DAX course here: www.myonlinetraininghub.com/power-pivot-course
Great video as usual .
I believe when we upload files from a folder in power query and select the first file for sample query, if the position of the the file changes in the folder , it effects the query, right? Is there anyway to make it dynamic ? Other than selecting a specific file for sample query?
Well, about an excel function that I wanted to learn better is OFFSET, 😊 thank you
Glad you liked it 🙏 Good point about the position of the file in the folder changing. You could make it dynamic with a modification to the functions to pick up a parameter instead.
For OFFSET, please see this video: th-cam.com/video/sRE9jAMqF7A/w-d-xo.html
@@MyOnlineTrainingHub Thank you for the response:) I have watched the OFFSET tutorials, it’s really simple and that’s what actually I wanted, starting from a basic use to a complex!!
And , I don’t know much about using Parameters in power query, if you have done a tutorial already, pls let me know.
I have a question about combining files from folder, first row of all my files are header, so when I promotes first row as header , it doesn’t applying to the rest of the files, they remain in data and I have to filter to remove them. Is it common? Appreciated your reply. Thank you 🙏
Thank you so much for this tutorial! ❤
You are so welcome!
how do I get the data for practicing of this training?
The link is in the video description.
Can you use this with warehouse stock? Love watching your videos I get some great information from them
You can use these techniques with all sorts of data.
thank you very much.
Welcome 😊
Which function replace vstack function? I don't have Microsoft 365.
There’s no replacement/alternative for VSTACK.
Good stuff "mate", thanks again! I would like to know more about power query and then specifically how to handle data from multiple sources which contain different deliminators and thousands/decimal settings. For example, masterdata is loaded from an Excel file (with US/English settings on laptop) and the sales table from a CSV (produced from a Dutch database). I somehow always struggle to get this right.
Thanks for watching! Each of your queries would have to be created separately and cleaned before appending/merging them (assuming that's the end goal). I'm not sure there's an easy way to help you remember the steps as such, but once you set up the queries, you should be able to reuse them for future files.
@@MyOnlineTrainingHubThanks for getting back! I just found the "using locale" option, in the "change type" submenu. And it seems I can deviate from the system settings like this.
This is amazing, thank you Mynda.
Thank you for watching and for the support! It means a lot 😊🙏
Hi Mynda, could you share a link in your videos where I can find out how to add funds to my stocks portfolio account,
I am uk based and use mainly uk stocks but now I have a couple of funds us as as India and uk but I can’t seem to find the correct way to add them to my excell
I have the correct ticker but I cant find how to add them in, as excel wants to give me other tickers 😂
Any help would be greatly appreciated thank you
Kind regards kev
I don't have a video on this exact scenario, but I suspect it will require the India stock exchange followed by the ticker.
Ok thank you for your very quick reply..
For example i put theses 2 in like this into spreadsheet HSBC FTSE 100 INDEX INCOME C, but the reply i get is this HSBC HOLDINGS PLC (XNYS:HSBC) and doesn't see anything else
Vanguard U.S. Eq Idx £ Acc, and i actually hold these two in my account. so i know they exist 🤣
@@MyOnlineTrainingHub
Why are Excel sheets limited in rows? I was forced to learn Access databases (1990s) because of Excel limits. Are sheets still limited?
There are now over 1M rows available in the grid (a lot more than in the 90s). However, if 1M is still not enough, you can use Power Query to load your data into the Power Pivot data model where you have unlimited rows of data you can work with. From there, you can build PivotTables to summarise and analyse the data. If you'd like to see these tools in action, check out this video: th-cam.com/video/TK0CJBaqvnY/w-d-xo.html
does anybody know how to enable de AI field to ask questions in the data analysis part in power query? I don't have it
Power Query doesn't have AI...yet.
When I was working, I did a kind of recertification using Excel, and the data was massive. Although I had a few days after training to go about it, I was not familiar with it.
Do you know what that could be?
No idea, sorry.
@MyOnlineTrainingHub It's reconciliation, not recertification.
By and far the lambda function has me the most confused and I wish i know where and how to apply it.
I hear you! 😁 check out my LAMBDA video here: th-cam.com/video/cGxjWOY8h98/w-d-xo.html
I have not figured out how to work with excel in a good way for statistical analysis.
Lets say I have 10-20000 rows of data from four pumps in a factory. I plot a regression that shows there are some outlier points. How do I exclude these points moving forward in the statistical analysis (before I make more calculations or the regression analysis etc.) ?
In the plot I can see the x,y of the points, but going into the table with 10000+ rows it is a lot to scroll down and find the points and delete them. Using filter on the column dosnt work either as there are data for the other pumps in the same row that is not outliers.
In R/Python there are some options forexample making a new df excluding these points. But I dont an Excel equavalent to this way without ending up with alot of individual tables.
What if it is not outliers I want to remove, but only make the an regression analysis on a section of data and another analysis on another part.
You could possibly use the FILTER function to exclude the outliers and create a subset of your data for further analysis. FILTER function is covered here: th-cam.com/video/ZCQAweoAdOw/w-d-xo.html
I wish I understood better how I could automatize the integration of csv file and split concatenated values from one cell in dedicated Column with corresponding headers e.g in one cell (Length [mm] : 20; Height [mm] . 12; Depth [mm] : 5) tranform to columns with Header Column 1 : Length [mm] : each row value corresponding length e.g. 20 Header Column 2 : Height [mm] ....
Sounds like a job for Power Query. If you get stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
thanks
My pleasure 😊
Data files are not available for download now. Please help.
They are for me. What makes you say they're not? Follow the link in the video description, enter your email address to reveal the download button and then download the file.
Sorry , can you tell again who can have the ANALYZE DATA feature?Thanks - I cannot work it out how to add it...365office account and also personal 365 version license.
Analyze Data is available to all 365 users AFAIK. You don't need to add it. It should just be available on the Home ta.
@@MyOnlineTrainingHub Only for English version. I have the Czech version....Thank you for answer🙂
Dear Mynda,
Data files from January to November are not available for download.
Oops, try again now.
@@MyOnlineTrainingHub Tanks. 🤗
My issue is using Power Query to clean up data generated by badly designed reports. I have tried to use Power Query but can't seem to get data into the proper rows and columns.
Thanks for sharing! In future 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
Great video! I do like using Power Query and Power Pivot. Personally, I'd like to know more about the Lambda function.
Awesome to hear. I've done a couple of videos on Lambda, but I'll keep it in mind for more.
Excel has a native histogram feature under: Insert -> Chart -> Insert Statistic Chart
Yes, but unfortunately it doesn’t work with PivotTables, which is why I used the regular column chart.
Would be the world of List function in Power Query.
Thanks for sharing. Check out these videos: th-cam.com/video/U97r2_O8IyI/w-d-xo.html and th-cam.com/video/ljnlEcRxZlo/w-d-xo.html
HI there. first of all thank you for your intelligent videos , second : i would like to ask for your permission to use the data you used in the budget vs actual vs plan video ( I dont want the dashboard i just need the data sheet as it would help me in designing sample data for my clients . )
Glad you found them useful! You can use my data for non-commercial use.
@@MyOnlineTrainingHub sorry but is designing a budget file from my own with this data and put it on upwork as a portfolio would be considered commercial ?
List function within PQ.
Thanks for sharing. Check out these videos: th-cam.com/video/U97r2_O8IyI/w-d-xo.html and th-cam.com/video/ljnlEcRxZlo/w-d-xo.html
power query, my pdf bank statements can never be loaded
I wish I could learn VBA. I want to learn it so bad. I dont know where to start. I am Chartered Accountant and proficient in excel but VBA is the missing piece.
What do you want to use VBA for? Many times VBA is not required and it's even less useful now that we have Power Query to automate the gathering and cleaning of data.
how to learn all of this?
You can take my Power Query course here: bit.ly/powerquery0124
power query data cleaning 😊
Is the best 😉
Super
Thanks 😉
Nice shirt. 😊
Thanks! 😁
You are looking Beautiful.
Thanks for the video.
can you please make video on Substitute function in excel (Advance level).that i wish to understood better.
Thanks for the suggestion. 👍
power query more realistic examples compare to last video (containing 4 examples) explained
Thanks for the suggestions 👍
Power BI/Pivot (DAX Functions in a bit detail), I wanted to learn better
Thanks for the suggestion. Please check out my Power Pivot & DAX course: www.myonlinetraininghub.com/power-pivot-course
Power Query, everyone in my work is using it now and the advanced editor in it
Power Query is, indeed, a feature that many Excel users find hard to work with. I have a playlist dedicated just to it: th-cam.com/video/wk1s0wE5Gww/w-d-xo.html&pp=iAQB I hope you will find the answers to your questions there 😊
Power Query and M Language
Power query is my number 1 Excel skill you must learn 👍 check out my course here: www.myonlinetraininghub.com/excel-power-query-course
Oh man! Where was this video 13 years ago? 😉
To answer your question, it would be macros. BUT, from what i know and do, I've just stopped using macros because they "break" all the time, require maintenance, and can destroy the sheet, etc. 😎
Yeah, I try to avoid macros too 😁
index match
Check out this INDEX & MATCH video that'll get you up and running with it in a few minutes: th-cam.com/video/-4yCXpv-drg/w-d-xo.html
I wish it was. Data analytics
Hopefully this video will help you get started with that.
I enjoy watching your videos. I would appreciate seeing more videos that relate to workforce data to show staff numbers, gender, tenure, hours worked, CALD, ect.
This would be so helpful. Thanks.
Here's a video on a HR dashboard that might give you some ideas: th-cam.com/video/rsx43g7TBBs/w-d-xo.html
Hi, why do you use the average? Do you want to calculate an average of % GP (what meaning?) or % GP per country? this is not the REAL %GP!! Indicators (measures) such as % margin, % Discount, Price ... never calculated in ETL process.
Good questions. The inputs are up to you. The purpose of the video is to show you how you can use Excel to perform common calculations that you might ordinarily do in a more convoluted way.