Download File: http://people.highline.edu/mgirvin/excelisfun.htm
See how to use Power BI Desktop to import, clean and transform Sales Tables from Multiple Excel Files and consolidate into a Single Proper Data Set that can be linked in a Relationship to other tables, and then build DAX Calculated Columns & Measures for Gross Profit that can be used in a Dynamic Dashboard with Map, Column Chart, Line Chart, Card and Slicer visualizations. During the whole process we will compare and contrast how the process is similar and different from Excel’s Power Query and Power Pivot DAX.
The steps we will see in this video are:
1. (00:17) Introduction to entire process for Power BI Desktop, including looking at the finished Dashboard
2. (04:50) Import Multiple Excel Files From Folder
3. (05:44) Name Query
4. (06:02) Transform extension column to lowercase
5. (06:34) Filter Files to only include “.xlsx” file extensions
6. (07:05) Remove Columns
7. (07:18) November 2016 Power Query Update Problem
8. (08:05) Add Custom Column with Excel.Workbook Function to extract the Excel Objects from each File.
9. (09:40) Delete Content Column
10. (10:41) Filter to only include Excel Sheet Objects
11. (11:06) Filter to exclude sheets that contain the word “Sheet”
12. (11:40) Remove Columns
13. (11:51) Expand Data and Sheet Name Columns
14. (12:06) Change Field Names
15. (12:22) Change Data Types
16. (14:05) Add Custom Column to calculate Net Revenue Column then round Number.Round function. Then Add Fixed Decimal Data Type.
17. (15:59) Remove columns for Amount and Revenue Discount
18. (16:10) Close and Apply to add to Data Model
19. (17:05) Import Excel Manager Table. Change Data Types to Text. Close and Apply
20. (18:10) Create Relationship between Zip Code Columns
21. (19:03) Create DAX Calculated Column with the IF Function to Categorize Retail Data. Change Data Type.
22. (21:53) Create DAX Measures for: Total Revenue, Total COGS and Gross Profit. Add Currency Number Formatting with No Decimals Showing.
23. (24:28) Create DAX Measures for: Gross Profit Percentage. Add Percentage Number Formatting with Two Decimals Showing.
24. (25:35) Create Map Visualization for Zip Code & Gross Profit Data (Zip Code with relationship to Managers)
25. (26:20) Create Clustered Bar for Manager Names & Gross Profit Data (Zip Code with relationship to Managers)
26. (27:15) Create Clustered Column for Product & Gross Profit Data, with a Line Chart for Gross Profit Percentage
27. (28:19) Create Clustered Column for Payment Method & Gross Profit Data, with a Line Chart for Gross Profit Percentage
28. (28:45) Create Slicer for States.
29. (29:00) Create Card Visualization for Total Revenue, Total COGS, Gross Profit and Gross Profit Percentage.
30. (29:57) Summary
Learn Power BI Desktop Basics. Introduction to Power BI Desktop. Getting Started with Power BI Desktop. Create Impactful Reports With Power BI Desktop. Microsoft Power BI.
Hi there, While following through your tutorial I have noticed at 23:04 when you are applying Currency Format I cannot seem to use the Formatting feature as it is disabled for some reason. Is this due to the fact that Microsoft has changed some functionality since the time you recorded your video or perhaps some of my settings are not optimal. Your advice is solicited. Thanks.
Sir you are doing an excellent job here. I checked the site for downloading your training files and was overwhelmed by the amount of stuff you have. I can truly appreciate the time and effort you have spent in this gigantic task. My question for you sir is if someone wants to follow your tutorials both on Excel and Power BI what order should they follow?
For instance I attempted to follow along your EMT 1366 ahead of watching the EMT 1360 video and was going nowhere. Once I saw 1360 it makes a lot of sense to me how Data Transformation is performed.
I request you indicate some kind of sequence on your web site or with in the YouTube channel suggesting an order in which to follow the trail of your videos.
I don't want to sound as if I am complaining. If it was up to me I'd nominate you for sainthood. Thanks again for opening a world of possibilities and opportunities for me.
Sir, thanks again for your prompt response. I have some questions around 1360 video. The part where you are importing a folder in to Power BI desktop. You have indicated that Headers need to be added when importing a folder. I wonder how will I learn what steps need to be done and in what specific order?
I wonder if you can recommend a book or source that can be referenced for these kinds of situations. Thanks again you are a phenomenal resource with an admirable sense of customer service. I wish there were more people like you in my life.
No, no, all of us humans have a hard time with that. The truth is that I have classes (playlists) that are systematic and complete when you watch the videos in order, like Excel Basics, Excel Advance, Array Formulas; but I have no such playlist for Power Query, PowerPivot, DAX and Power BI Desktop. I am sorry about this - but I am working on it and over the next year these systematic and complete playlists (classes) on the Power BI tools will be released.
Thanks for the feedback. The links for the Playlists are fantastic. However one clarification I need is whether the Playlists are to be followed in the sequence of numbering for the videos or the order as they appear in the Playlist? For instance the Power BI playlist displays the EMT 1366 video on top and I watched it first but was lost. However when I saw 1360 it made a lot more sense. The sequence of following the Playlist is very important for me personally. Some people may be smart enough to follow regardless of the order but I am a bit obtuse that way.
Thank you for the kind words, Eddie!
For Excel, all of the tricks of Excel are presented systematically in the two classes that I teach for free at YouTube:
1) Excel Basics Playlists: https://www.youtube.com/playlist?list=PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
2) Advanced Excel Playlists: https://www.youtube.com/playlist?list=PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw
then if you want Power BI, you can choose from these playlists:
1) Power Query: https://www.youtube.com/playlist?list=PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK
2) Power BI Desktop: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k_h8ORYyh7waGfuiiufu6H
3) DAX Formulas: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nglJ9yX2XT5-K1A_AKHpvW
Also, over the next year, starting in about a month, I will post an entire class playlist of Power BI in a systematic playlist - so watch for that.
If the videos help you, please help me by Subscribing and clicking Thumbs Up on every video you watch and leaving comments when so inspired to do so.
Awesome tutorial - a first for me in Power BI. Very cool. Downloaded your files and followed along :)
Same comment as Jian below on ZIP codes populating outside of US. Not sure the map has a country filter. It looks like it can work with Lat/Lon. I tried to load the same data set through PowerQuery and plot on Power Map; the same issue - which is a MS Office bug :)
I found the same issues on both ZIP codes. This may be a bug on how the map sees ZIP codes. Will need to investigate more on this. The fact there is a state name doesn't seem to impact the map settings. Looks like Microsoft is still debugging Power BI.
Hey! thanks for spreading excel love! I have a hurdle, do we need any specific formatting of the data to import into Power BI. Does it imperative to "Pivot" or format data with any innate excel formats before importing it to BI? Thank u
You are a hero ... thank you so much for sharing your knowledge. And for a not native English speaker you are very clear ... I have been strugling with formatting a number in a table (no decimals) and found it very frustrating that you cannot change the formatting, Now i realize you have to manage this on a dataset level :)
Enjoy your weekend.
kind regards from Holland, André
Yes, over the next few months I will create an epic Data Analysis / Power BI free YouTube Video class that will cover Excel Formulas, Standard PivotTables, Power Pivot, Power BI Desktop and DAX in full detail. Keep watch, it should start in about a month.
I got an error on the visualisation, i got the map but when selecting gross profit i get the comment: couldnt load the data of this visual SalesZipData Total revenue the function SUm takens an argument that evaluates to numbers or dates and cannot work with the values of type string
thanks for the video. I've been trying to setup percent complete S-curve (x-axis = dates, y-axis=percent %) for the project, which shows the baseline (planned) percent complete vs (actual to-date + forecast). My input source of percent complete is coming from the scheduling software (Primavera P6). I don't see any sample on how to setup an S-curve anywhere. Would you able to help me out on this?? thanks
Great presentation! I've started testing Power BI in a large, corporate environment as we consider replacing/augmenting some other BI tools. This video is a great introduction that I'll be sharing to help introduce new users to the features and functionality.
Make sure you're getting these highlighted in the Power BI forum!
I've found the Power BI community that's available through powerbi.microsoft.com to be very helpful. On the homepage, check Learn > Community for some links to discussion and help forums. I think your videos would be an excellent resource to go along with Microsoft's Guided Learning videos. The Microsoft training materials are very good but I found your videos to be a little more helpful in bridging the gap for the end users. Also, a lot of Excel users are familiar with your other lessons and that makes the transition that much easier.Thanks for the other link, too. Please keep up the good work!
Here is a second Intro Video i did that may help you also:
Power BI Desktop: Build Data Model, Get Data, DAX Formulas, Visualizations, Publish 2 Web (EMT 1366)
I am glad that this video is helpful and that it will help you introduce your co-workers to this tool. When you write; "Make sure you're getting these highlighted in the Power BI forum!" I am not sure what you mean?
Hello All!! I am looking for a feature like.. if we select bar chart from slicer then bar chart will b displayed and if we select table then it will display table by hiding bar chart.. Is this possible at this moment? If not then what is alternate option??
i have report daily basis showing budget & last year and this year daily basis and month to date year to date comparing all can i use power pivot table and power PI to make it
i am already make the report using vlookup & sumif & sumifs macro to post date from data entry but i am thinking using power pivot or power query & pivot table
i have formula
the result is
but it only text
the result is
but it only text
so how to convert result to be formula not text
Michael, thank you so much for this video and for many other lessons! You have literally discovered the new great world that is like a gift box! that is pretty amazing (c) =)
Wish you a Merry Christmas!
Hi Mike, thank you very much. The video is AMAZING.
I'm in Chile and I'd like to apply the map tool with Chilean Cities Zip codes.
Is it a data of Zip Codes in Power BI from this country or that tool just apply in the US?
I do not know. I am just learning Power BI Desktop, but my guess (based on past Excel experiences) is that Microsoft probably has built it as US-centric. However, I do know that it uses BING mapping, so it might accommodate... I also know that Microsoft is working as hard as they can to accommodate the whole world into Power BI Desktop, I just don't know if they are there yet...
hooooleee doooleeee Mike, that was awesome. I work in ticketing and have just started using this Power suite of tools, I'm so blown away at the power and functionality. It is going to transform the way we report. Thanks heaps for your amazing tuition, you are the best! And have a great festive break😁
You are welcome! Yes, this new free tool is an all-in-one awesome thing. Once we are all hooked, they will charge us. That said, Excel still can do many, many more things than Power BI Desktop and so will still be with us forever, but for the Data-Driven Dashboards and Reports, this is just amazing.
Hi ExcellsFun and thanks for your reply. I enjoy your videos and picked up many tricks already. I work with clients that provide me with similar data, but in many-many different formats. My goal is to visualize and analyse their data in fastest possible way.
It is complex, because you have to do many things: Import, Clean and Transform Data, then Build relationship, make DAX Formulas and then build visualization. But for people who do this, what is REALLY amazing is that all of this can be done in 1 tool!!! In that way it is amazing. Here at excelisfun at YouTube, I try to post Excel videos about Basic all the way to complex topics. Hopeful you can find something that is helpful to you. What are you interested in?
You do not need to calculate payroll premium if your policy is on a stipulated billing cycle.
Sample Payroll Report.
However, if your policy is not yet on a stipulated billing cycle, this is typically what you will see when we send you a payroll report.
A split payroll report is sent when there is an Anniversary Rating Date on your policy, which is the month and day that rates, rating plans and rating systems are initially applied to a policy in force and each annual anniversary thereafter. Your payroll will need to be annotated for each period specified.
Sample Split Payroll Report.
We will need a complete employee job description before we add the classification to the policy. Please do not report payroll in the new classification until it has been reviewed and endorsed to your policy.
Job Duties Questionnaire.
We recommend you keep a copy of your previous payroll reports and payroll records for at least seven years, as you would your tax records.
Submitting Payroll Reports.
There are three different ways to submit your payroll reports.
State Compensation Insurance Fund P.O. Box 7441 San Francisco, CA 94120-7441.
Free payroll reports.