High-Income Excel Skills Worth Learning in 2024 (Free File)
HTML-код
- Опубликовано: 1 июн 2024
- Essential Excel skills that will help you get promotion and earn more.
👩🏫 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.
➡️ Download the example file here and follow along: bit.ly/file240319
Believe it or not, more than half of the job listings across a wide range of industries identify Excel as an essential skill. From administrative assistants to sales managers, to data analysts, to name a few. But the turning point comes when you must excel skills that can either make a company money or save a company money, or preferably both. In this video, I'm going to share three Excel skills that will put you on the fast track to the top.
LEARN MORE
===========
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetraininghub.com/e...
🎯 FOLLOW me on LinkedIn: / myndatreacy
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
==============
0:00 Excel skills are in demand
0:41 The first skill you need to learn
7:18 The second skill you need to learn
22:15 The third skills you need to learn
28:38 The next step
#Excel #ExcelTutorial #HighPayingSkills 
Наука
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
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.
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. :)
As always, you are SENSATIONAL!
Thank you!
Thanks so much, Charles!
Wow Mynda that was IMPRESIVE! Love your videos
Thanks so much!!
Pinnacle of professionalism and innovation
Thanks for watching 🙏😊
This was a very nice overview! Thank you.
Glad you enjoyed it! Thanks for watching 😊
Mynda your tutorials are awesome magical to me....thank you so much
Mo
Thanks so much, Mo 🥰
Brilliant!! Man, I love your videos
😁 thanks so much!
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.
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: ruclips.net/video/gOs7EC-FebE/видео.html
Consider taking a course. It is a small dollar investment that will give you a structured learning approach to gain confidence and efficiencies.
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 😇
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!
So much to learn as usual from your awesome contents. Thank you so much.
So nice of you 🙏😊
You are a valuable guru for Excel Learners. Wish you get a high paid from RUclips for your precious contents...Thanks Mynda
Thanks for your support!
Thanks much for detail guidance. It is a great lesson.
Glad you found it helpful!
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 😀
Inspiring stuff. Thanks for all your content.
Glad you enjoyed it!
This was great! Thank you so much!
Glad you enjoyed it! 🙏
Very well presented and easy to understand.
Glad you think so! 🥰
Excellent! I realy appreciate, Thank you 10/10
So glad you liked it! 🥰
Very helpful, thanks for sharing!
Glad it was helpful!
This was such a useful and well explained video, so easy to understand 🙏
Glad you think so! 🙏
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.
Excellent video as always!
Thanks so much 🥰
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!
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.
Oh Mami - yowiewowie, this is amazing - thank you for enlightening us !
My pleasure!
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.
Great video , many people underestimate the power of excel
Very true! Thanks for watching 🥰
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 🥰
Always amazing!
Thank you! Cheers!
Thank you
You're welcome 😊
Thank you!
You're welcome!
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 RUclips, you are bound to miss important details:
www.myonlinetraininghub.com/excel-power-query-course
www.myonlinetraininghub.com/power-pivot-course
And again it was superb.
Thanks so much!
Awesome learning video 👏
Thank you 🤗
Thank you 😊
You're welcome 😊
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.
Fantastic video.
Thank you very much!
Awesome.. thanks alot
Most welcome! 🙏😊
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.
Thanks
Welcome 😊
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 😊
Very Very Very Goooooood
Thanks so much!
thanks
You're welcome!
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 😁
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.
This is so nice
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.
🥰🥰 Perfect
Thanks so much 🥰
Amazing
Thank you! Cheers!
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: ruclips.net/video/sLW3NbeGDy8/видео.html
Whoa!! Thanks a lot for this. God bless you.
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.
Lovely❤
Thank you ☺️
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.
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
Heroo🔥🔥🔥😍
Glad you liked it 🙏😊
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.
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.
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.
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.
What about human maintained excels not generated from any ERP system like they use merge on top of excel or data headers starts from row 5 or 6? or each month file contains multiple tabs but the one common tab has inconsistent name? It takes more time to bring data in usable format of power query than to do everything manually.
I'd have to see your exact scenario, but Power Query can work with data that doesn't start on row 1 and data spread across multiple sheets etc., so I'd be surprised if you can't automate it.
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.
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.
Wow!
Glad you liked it 🙏
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.
Nice voice
Thank you!
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
Thanks for the great info! I create dashboards for clients with their sales data, but I need to email it to them. How will a document like the one you're using work if it's sent to another computer and Power Query can't find the folder that the data is in?
If the user doesn't have access to the folder location then they can't refresh the queries, but the report will still display the data and they can use the slicers. They just can't update/refresh the queries, but this is no different to how you currently do things.
Thanks@@MyOnlineTrainingHub 😀 I really enjoy your videos
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.
I am at the beginner level of using Excel. One day I would like to use Excel in advanced level.
Great to hear! I hope you find my videos helpful. If you want to get up to speed quickly, please consider my courses: www.myonlinetraininghub.com/courses
This is awesome, but can one of you please provide a solution because for me the date option is blurred out and I cannot do anything
What is the time stamp of the 'date option' you're referring to?
Can Tableau do all of the same stuff, is it worth learning how to do it in excel? I don't understand where the overlap is between Tableau/Power BI vs Excel. Understanding both is probably best.
If your company is happy to pay for Power BI/Tableau licenses then I'd do my reporting in them for the most part, but Excel skills will also be essential because it offers more flexibility than those apps for ad-hoc analysis and modeling etc. Personally, I think it's better to go down the Power BI path because the Power Query and Power Pivot skills are transferrable between Excel and Power BI and Power BI is the leading BI tool according to Gartner.
Companies let you go ESPECIALLY when you fix and automate everything for them... then they hire low budged data entry people at 1/3 your cost.
Especially when your boss sees your ability to automate his/her job too! 😁
Most companies don’t behave like that. At least with these skills you can more easily find yourself a company that values you.
If you are a Mac-end user of the dashboard, it wont load thr dashboard due to not loading the table from the data model. Seems to be a limitation of Excel in Apple products.Is there a way around it?
Power Pivot is not available in Mac and it's a long way off being available. Power Query is still under development for Mac and while it's getting there, it's still quite limited. You'd have to install Parallels or similar on your Mac so you can install Windows OS and then Windows Excel to get these tools.
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: ruclips.net/video/QKgS3hrrmvw/видео.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!
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.
Hello Mynda, Does it make sense to learn M language along with PowerQuery? I find it too challenging for me.
You’ll grow into it as you go. The major benefit of PQ is the interface that will support you really well in the process.
At some (more advanced) point of your evolution, you’ll start “fighting” with what the interface does - that’ll be your cue to realise you’ve outgrown it.
My first step (way back when) was to go to PQ, do an unpivot, and get back to Excel. Now I very much like to tinker in PQ - it has quite a few advantages on “Excel Classic”, although the latter is playing catch-up.
@@GeertDelmulle I get your point. Thank you. I have an okay control over PQ. M language seems so cryptic to me. There isn't any easy to learn resource available right now.
@ryan19899 Geert summed it up perfectly. In my Power Query course I cover the essentials of M. You can see more info and the complete syllabus here: www.myonlinetraininghub.com/excel-power-query-course
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
Are you ok with this being used as a portfolio project?
Just read the copyright notice. "This is for practice only" Got It!
Yes, it's probably not a good idea to use it as a portfolio project because if someone has seen my video, then they'll know it's not your work as such. However, please use the example file to learn and get ideas for your own projects.
Hi, when I selected two columns for getting order and shipping date deference- data tab in data and time portion got unable to use. That's why, I could not get the order and shipping date deference column. kindly guide me.
This is a locale issue. This video explains how to fix it: ruclips.net/video/QKgS3hrrmvw/видео.html
So does this savings translate into a bonus for Dave??
Dave aka Bernie, can name his price 💰 😁
Hi, there at the time of manage data model am getting multiple Pivot tables more than 2 everytime am starting am facing this issue can you please resolve it
That sounds odd. Please post your question and Excel file/screenshots on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I've started using PQ for my own effectiveness to make my routine more easy, but my company counts it just ok...
I hear you. One of my prior jobs had everybody individually read through 8-14 pages of calendared hearing issued by the county every week for any of our client's names (which were like 100+). Besides being tedious, I was scared I would miss one or not recognize some name (fairly new to the company at this point). I highlighted and copied the entire contents of the pdf into Excel and wrote a code that would search it for the contents of a list of clients and remove all the data that wasn't a match. This turned an error prone 10-minute process that had to be done every Monday into an errorless 10 second process that could be done at any convenient moment. When I showed it to my boss, he was dismissive and insinuated that I was just lazy. He insisted on doing it manually and told me to do the same. Not much incentive there.
Great to hear you're making use of Power Query.
Hi Manda, I have been following some of your videos but with this one I am frustrated as it seems my excel is greyed out and I cannot click on some function for example when you add the column to subtract the days. The date button is greyed out and cannot be used. I am not quite sure why I can't do some of your functions as displayed in this video?
I suspect the data type for the date column is not correct. If your dates are formatted mm/dd/yyyy then you will need to fix them using the 'Change Type Using Local' option. See this video for how to do that: ruclips.net/video/QKgS3hrrmvw/видео.html
@@MyOnlineTrainingHub You were spot on! Thanks Manda.
@@MyOnlineTrainingHub Thank you Manda, you were spot on. In this video it shows how to convert Budget to whole number, however I tried that with the Discount column as well, I even tried the locale option. I still get the error "we couldn't covert to number". I tried different options to convert it to number or decimal number first to get percentage but nothing works.
Please post your question and sample Excel file/screenshots on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thank you I will definitely do that as I really would like to solve it on my own.
Mrs Mynda does your own company hire? Is there a platform we can send a CV?
No, sorry.
@@MyOnlineTrainingHub No CVs only CSVs :)
😂exactly!
Hi, My data doesn't fit still., (May-Mar) is the data and It's missing some of the months in between. ( Uploaded 02 csv files)
You have a locale issue as mentioned in red text in the front of the Excel file you downloaded. Please see this video for how to fix it: ruclips.net/video/QKgS3hrrmvw/видео.html
I have tried to add the lookup tables to the Power Pivot using the process outlined in the vid. They are not showing up as additional tabs - any ideas on why this is happening? Many thanks
Did you select 'add to data model' when you clicked 'close & load'?
Yes as outlined in the vid there are 2 radio buttons to click on the splash screen "Only create connection" and then "add this data to the Data model " then go back to the Power Pivot and click refresh - there is no additonial tabs loaded with the info from the lookup tables. Any help would be appreciated, thanks
@@MyOnlineTrainingHub I am having the same problem as @trovis8527 and did select add to data model too, they don't show up as tabs, although I saw them as steps on the query and had to delete them when I saw them listed with (2). Is there a solution?
@@MyOnlineTrainingHub I am having the same problem. No new tabs show in the data model.
@trovis8527 and @susanmitchell8018 it sounds like something went wrong along the way. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
What's the difference between Power Query and SQL because both can grab data from different sources.
Power Query simplifies data preparation for users not familiar with programming, providing a wide range of data connectivity and transformation capabilities within a graphical interface. It is not limited to only getting data from relational databases. It can get data from the web, PDFs, Excel, text/CSV files, SharePoint, OneDrive and more.
SQL, on the other hand, is a powerful tool for directly interacting with databases, requiring technical knowledge to leverage its full capabilities. It is limited to working with data in relational databases.
@@MyOnlineTrainingHub Thank you.
Do you have the SKU and state file to download as well?
Yes, it’s all in the download file. The lookup tables are in the Excel file.
I only see the sales 2021 - 2024 zipped.
AH HA, I think I found them!! Nice work, by the way!!!
Glad you found it. Great to see you're giving it a go too. I hope you can use these techniques to save time.
can we do that on power bi?
Yes, but not every company has Power BI, so Excel has broader application. The same Power Query and Power Pivot tools are in Power BI, so having these skills gives you double the power 😉
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.
Where would we find the data tables? can not see them in the downloaded file :(
In the zip file you downloaded there is a folder called 'Data'. The data tables are in that folder.
Found it - thank you - was having my moment :)
Please, I had issues with making the subtracted date, and I’ve been trying to resolve it, its still not resolved, can you help out?
I had the same issue, you may want to change the column data type to date, otherwise elaborate further on the issue you encountered
@@FSY-hq3pf I already did that, But it's still giving the values as numbers.
@@temiloluwajohn6426 But the values should be numbers.
I’m not able to get the data she used for mapping either, anyone has that ?
💚
🙏
I'm stuck at 4:29. I dragged the Shipping date column next to Order date and then tried to subtract days but it doesn't seem to work. When I clicked Add Column and chose Date, the Subtract Days option was not clickable for me. Can anyone help? Thanks.
I suspect the columns aren't correctly formatted as a Date data type (see Home tab for this setting).
I couldn't subtract dates in columns either.
Excel cannot recognize the dd/mm/yyyy format date in the CSV file, and yet when I try to make it a date from the transform tab/Detect Data Type after using Combine & Transform Data as 1252: Western European (Windows), it recognizes the date and month upside down, so even if Excel is able to do the subtraction, it will be an incorrect Days to Ship.
It seems this is due to Windows regional settings rather than Office date settings.
Anyway, to attack the power query in this video to the end, I opened the CSV in Excel and put the following function (convert dd/mm/yyyy to yyyy/mm/dd) in the blank column (e.g. Cell "O2") to convert and overwrite the date as value, then save Excel.
for Ship Date
=+RIGHT(M2,4)&"/"&LEFT(RIGHT(M2,7),2)&"/"&IF(MID(M2,2,1)="/",LEFT(M2,1),LEFT(M2,2))
for Order Date
=+RIGHT(D2,4)&"/"&LEFT(RIGHT(D2,7),2)&"/"&IF(MID(D2,2,1)="/",LEFT(D2,1),LEFT(D2,2))
As a result, Excel recognized it as a date and was able to subtract it correctly.
By the way, how do I automatically change the background color of the A1 “Sales & Shipping Dashboard” in the Dashboard tab at 24:22?😅
❤👍🙂
Thanks for watching 😊
How is this different from Power BI?
The same Power Query and Power Pivot tools are available in Power BI, but many companies won't pay for the Power BI licenses, so Excel is a great alternative. Also, Excel is better for analysis, whereas Power BI is more for reporting.