Use Excel Like a PRO | Learn Power Query, Power Pivot & DAX in 15 MINUTES (project files included!)

You’ve just been hired as a Data Analyst for Maven Electronics, a global electronics retailer.
It’s 4:00pm on a Friday when you receive an urgent email from the VP of Sales, asking you to build a brand new Excel report for regional sales managers. To make matters worse, the data is over the place - SQL servers, CSV files, even static PDFs - and she needs it first thing Monday morning.
Yikes 😬
In this hands-on demo, Maven Analytics Founder and Lead Instructor Chris Dutton walks through this business case while showcasing the power of modern Excel tools like Power Query, Power Pivot and DAX.
You’ll start by using Power Query to extract, transform, and load data from external sources like SQL databases and CSV files.
Next you’ll build a relational model to join the tables without writing a single formula, conduct some exploratory analysis using Power Pivot, and define calculated measures with Data Analysis Expressions (DAX).
Finally you’ll use Pivot Charts and slicers to design a simple interactive report that the sales managers can use to analyze revenue trends and product performance.
All in a matter of minutes 💪
Microsoft Excel is an incredibly powerful data analytics platform, yet less than 1% of users know how to leverage its most powerful business intelligence tools.
These skills will not only allow you to work smarter and faster in Excel, but also help you build foundational database and ETL skills that can easily be applied to tools like SQL or Power BI.
🔗 Helpful Links:
🎉 Maven Analytics Spring Sale - Save 40% off Pro plans! 🎉
mavenanalytics.io/spring-savi...
👉 Downloadable demo files:
maven-datasets.s3.amazonaws.c...
👉 Excel Specialist Learning Path:
mavenanalytics.io/path/excel-...
👉 Follow Chris:
/ csdutton
👉 Follow Maven Analytics:
/ maven-analytics
⏱️ Timestamps ⏱️
00:00 - Intro
00:34 - Project Brief
01:05 - Dataset Review
01:45 - STEP 1: Connecting data with Power Query
02:16 - Connecting to a SQL Database
04:39 - Connecting to a CSV File
05:53 - Connecting to a PDF
07:26 - Creating a Calendar Table
08:52 - STEP 2: Building a Relational Data Model
10:20 - STEP 3: Exploring Data with Power Pivot
11:20 - STEP 4: Calculating Measures with DAX
12:03 - Calculating Total Orders with DISTINCTCOUNT
12:44 - Calculating Total Revenue with SUMX
14:10 - Building an Interactive Excel Report
16:45 - Project Recap & Free Resources
At Maven Analytics, we empower everyday people with life-changing data & AI skills.
Head to mavenanalytics.io and master in-demand tools like Excel, SQL, Power BI, Tableau, Python and more. Design your own personalized learning plan and get started for FREE!
Looking for more opportunities to sharpen your skills? Explore sample datasets and practice solving real business problems with unique, expert-led guided projects: mavenanalytics.io/guided-proj...

[@MavenAnalytics]

Thanks for watching! If you liked this and want to learn more, check out our self-paced courses, guided projects, and create your own free personalized learning plan! 👉 mavenanalytics.io/

[@swatidas7069]

Can I use your course for free as I am unemployed since long. I want to learn from you.

[@muraliiyer7850]

Wonderful, without PowerbI, Excel itself has all facilities like PBI. Great learning today, thanks a lot.

[@JohnDChaney]

Nice work. Not only was the data analysist work explained well, the presentation is top notch.

[@MavenAnalytics]

Glad you liked it!

[@Chris-at-Maven]

Glad you found it helpful!

[@kishan0705]

This is Amazing, I got to know how I can swiftly use Power Tools in just 17 minutes. Thanks,

[@Funfunny820]

I bought your course still i am watching it is very nice

[@santiagolopezgallo4736]

This is just mind-blowing 🤯

[@Chris-at-Maven]

Crazy powerful tools! 💪

[@christianwiah4526]

Cannot disappoint as always. Super!

[@Chris-at-Maven]

Glad you found it helpful!

[@omolerebeautyolatawura5533]

This is the most powerful video on Data Analysis i have watched. Amazing! thank you so much

[@Chris-at-Maven]

So glad to hear that - thanks for the feedback!

[@zeeshansabro3163]

Great help. You really make my things easy through very interactive and easy-to-understand simulations.

[@Chris-at-Maven]

Glad you found it helpful!

[@lisitashamatutu1140]

Excellent work maven made this look so simple

[@muneebbolo]

Amazing to see this great tutorial ♥ Thank you so much, Chris.

[@farifarahani4641]

Excellent content and presentation. Subscribed. Interested in learning more!

[@muhammadsabirhussain1503]

Thank you so much. simple and worth learning.

[@somnathray3492]

Simply Love it Chris! Thanks for showing Want more on DAX calculations thnks in advance

[@israelking975]

Maven Analytics? YOU ROCK. Mav A For PRESIDENT. AWESOME WORK BRO.

[@Chris-at-Maven]

Haha thank you!

[@user-rf2bp1dg5f]

Wanna like this thousand times the best teacher❤️

[@Chris-at-Maven]

Thank you for the feedback! :)

[@KKB-tt4lj]

Well explained! Thanks!

[@thew5611]

i subscribed after watching this. ive never seen how power pivot and dax are used in excel. very helpful video.

[@Chris-at-Maven]

Thanks for subscribing!

[@asifjambagi8732]

It's just awesome, thanks for this video

[@StephanieDavis-vu3bu]

That was total AWESOMENESS!!!!!

[@Chris-at-Maven]

Glad you found it helpful!

[@wilfredoseiwusu3507]

This is awesome. Thanks Chris

[@Chris-at-Maven]

Thank you!

[@uc7602]

I just subscribed too! Thank you for including the DAX formulas for the calculated fields. This video was easy to understand and extremely useful. Thank you for saving me so much time!!!

[@Chris-at-Maven]

Glad you found it helpful, and thanks for subscribing!

[@shemustnotbenamed]

As always👏 Thank you for this awesome content.

[@MavenAnalytics]

Glad you enjoy it!

[@solarwinds-]

Great Vid, concise, to the point without side stories. The bosses will be amazed at the efficiency. But I fear a good performance would be rewarded with more of the same. Definitely use this, BUT don't let the bosses make the same request repeatedly. The first time is ok but politely let them know this can't be a habit. You work to live, not the other way around. I am SO GLAD I no longer have to work.

[@michellewilliamson9835]

Excellent!!! Thanks 🙏

[@mikeguetti8349]

Wow this was fascinating!! I never knew excel was so awesome. THANK YOU!!

[@Chris-at-Maven]

It really is!

[@PK-cj1pg]

Added knowledge. Thanks!

[@khinyaminn6293]

Awesome. Thank you very much. The best teacher 🙏

[@xeeshanahmad8757]

Yes💪, I did it. Thank You very much .

[@user-ep7dq5wn9]

This was awesome

[@Chris-at-Maven]

Glad you enjoyed it!

[@akamike2101]

Thanks Bro. you are one in a million

[@yuhooh]

Thanks Chris, I've follow you from LinkedIn course.

[@eliadnahum7338]

Solid tutorial; super helpful! any additional videos on DAX specifically?

[@MCdat123]

Awesome, thanks

[@Chris-at-Maven]

Glad you enjoyed it!

[@hadiuzzaman]

Very impressive.

[@erickshioso8227]

Wow! Marvelous Maven Analytics. Had to subscribe after watching this. Otherwise i need to have a word with George from IT!😂

[@Chris-at-Maven]

Thanks for the feedback, and for subscribing! George thanks you :)

[@Anthony50009]

Enjoyed working through this example using the sample data provided and was really impressed with what can be achieved in excel without necessarily needing to use Power BI. Am in the UK so had an extra step to try to work out how to change the dates in the sales file from mm-dd-yyyy to dd-mm-yyyy. Thanks for the valuable content Chris!

[@Chris-at-Maven]

Nice! Did you use Power Query's locale option, or manipulate the actual date values?

[@Anthony50009]

@@Chris-at-Maven I eventually found that it is an easy step to highlight the date column in power query editor and right click the column header and select change type - using locale to date type and locale English (United States)

[@Chris-at-Maven]

@@Anthony50009 nice, that’s what I would have suggested trying first 👏

[@swatidas7069]

I am also from UK

[@DatawithHimanshu]

Just Amazing

[@Chris-at-Maven]

Thanks!

[@joedeary2717]

Nice! 🎉

[@Yasir7184]

This is Awesome

[@Chris-at-Maven]

Thanks! This was a fun one to shoot :)

[@lisaward611]

My hero! ❤❤

[@Chris-at-Maven]

🦸‍♂😆

[@DiggaTheWolf]

Absolutely fantastic

[@Chris-at-Maven]

Thanks, glad you enjoyed it!

[@SANDATA764]

So freaking good , thank you

[@Chris-at-Maven]

Thank you, this was a fun one to put together 😊

[@SANDATA764]

@Chris-at-Maven, we need to see in different platforms like big data. As you know, most enterprises are using the Oracle database, so please make more projects based on Oracle.

[@Chris-at-Maven]

@@SANDATA764 we’re starting to build some new courses in cloud/analytics engineering soon - stay tuned!

[@diassetefle9775]

Very useful tutorial,👍👍👍

[@Chris-at-Maven]

Thanks for the feedback!

[@fewazbenachi6081]

I subscribed in the first 3min of the video, ❤❤❤. Wonderful and great 👍

[@fewazbenachi6081]

I have question, after doing all work in excel, I can use power bi without any more formatting?? Is it right

[@Chris-at-Maven]

@@fewazbenachi6081 you can import an entire Excel model into Power BI, which preserves your model, DAX measures, etc.

[@longager]

Great and informative video! A question: Wouldn't it be possible to do all the steps in Power Query? E.g. use the 'Merge' function in PQ instead of building relations in Power Pivot. The reason I am asking is that when you merge big datasets i PQ it often gets very slow so maybe this method with using Power Pivot is a better practice - or is it more to show the different options available? Thx

[@sallyho146]

Thank you for the video. I use Dax formula to write Text over value for monthly status. That’s very helpful for users who use the text to track the expenses against the headcount. Btw, do you know how many DAX formulas are out there?

[@v-7815]

HOLY MOLY

[@ajibolaazeez104]

Hi Maven, Thank you so much for the great work. Can you please share the source of the data you used for the analysis? I would like to practice with them. Thank you

[@Chris-at-Maven]

Link in the description!

[@Amitosh86]

Great video! Thank you so much for putting it together! With the help of your video, I created a file that pulls data from several tables in an ODBC which requires monthly refreshing which does take a lot of time. - Is there any way to make it so that instead of ALL tables being refreshed, only certain ones do? - Is there a way to make it so that data within a table only refreshes from a specified date? For example, if the data for the years 2018 to 2023 is never going to differ from refresh to refresh, set it up so that that specific data doesn't change but everything since 01/01/2024 refreshes? Hopefully, I'm making sense. Thanks again!

[@Chris-at-Maven]

Yes in the Queries & Connections pane you can right click a query, view properties, and deselect the "Refresh this connection on Refresh All" option. As for loading only incremental data, there are a few ways you could potentially approach it depending on the context. I'd recommend searching the Microsoft Answers Forum to explore some options!

[@Amitosh86]

@@Chris-at-Maven thanks Chris!

[@Wowdex8]

hello, on the DAX revenue formula, how is it determining which quantity row matches the price row in the other table? also, what is the reason for a calendar table when you can use the date from the quantity table, then group in the power pivot by month if you wanted? thanks!

[@Chris-at-Maven]

Good questions! For the Total Revenue formula, the RELATED function acts like a lookup, that basically traverses the relationship between the Sales and Products tables to retrieve the correct unit price for each row in the Sales table. As for the calendar table, it's much more flexible (and scalable) to have a dedicated table containing any date/time fields you might need, including ones that aren't available in the default PivotTable grouping tools. I also tend to avoid PivotTable grouping because a) it can get messy, and b) those grouped fields ONLY exist in the context of that pivot, and can't be used in other measures (this is the same reason we always recommend explicit vs. implicit measures). Hope that helps!

[@user-me8xb1wi5g]

When you take the course is it edited like this?

[@gurvindersingharora5301]

Hello Sir, Thank you for your useful videos on dax. Sir, when to combine ALL function with Calculate + Filter Function together in Dax ? Could you please make a video of it in detail soon 🙏 !

[@Chris-at-Maven]

Use ALL when you want to override any existing filter context (like forcing a grand total calculation, for example)

[@sohailarshad426]

Thanks ,some pretty good tips , But I have some problems following 1-My Sub- categories split out in another column 11:47 2- Dax Formula always gives an error , I wish I could upload screen shots.

[@eddiemendez4766]

I tried importing data from a pdf that had multiple pages but only the first page had the headers listed. All subsequent data after the first page oriented to the right of the data from the first page. I could not figure out to get them to all line up under the headers from the first page.

[@lovelyblackskin4565]

His Udemy on this is priceless

[@asdksd3482]

Can it be even better done with Power BI? Please enlighten me.

[@Chris-at-Maven]

You could definitely follow these exact steps in Power BI! It's less about which is "better", and more about which one is the right tool given the circumstances. The beauty of learning this stuff in Excel is that it makes the learning curve to Power BI so much smoother, since they are build on the same analytics engines

[@MaddyIndia]

Is PowerQuery part of standard Excel program?

[@Chris-at-Maven]

Yup it's built into Office 365 and pretty much any modern version of Excel

[@CaribouDataScience]

Wait! I wanted a line plot with one line for each country?

[@Chris-at-Maven]

You can visualize it any way you’d like, just drop the country field on your PivotTable rows and Revenue as values

[@scottishlass1740]

Not all these features are available on Excel on Mac unfortunately😥

[@CaribouDataScience]

Why did you use sumx instead of sum?

[@Chris-at-Maven]

SUMX allows us to calculate revenue on each row, then sum the results (vs SUM, which is just a simple aggregation)

[@abayomiopakunle2239]

How can we get the datasets to practice with?

[@Chris-at-Maven]

Check out the link in the description!

[@abayomiopakunle2239]

@@Chris-at-Maven Thank you. sorry to trouble you. After loading the sales data from power query editor, the sales data in the queries and connections pane displayed, "62,884 rows loaded, 38,540 errors" I noticed that the 'order date' column contains all the error

[@kunjukuttappan186]

Total Revenue =SUMX(Sales[Quantity]*RELATED(Products[Unit Price USD])) is not working for me. Can you please help. Thanks

[@Chris-at-Maven]

Remember you need the Sales table argument first, then [Quantity]*RELATED(Products[Unit Price USD])

[@kunjukuttappan186]

@@Chris-at-Maven Thank you so much! I missed that comma in between Sales Table and Quantity Column . It took 15 hours for me to sort that out.😋.

[@Chris-at-Maven]

@@kunjukuttappan186 the first argument of SUMX is a table reference (Sales). All the others are column references, we just don't need to qualify the [Quantity] column with the table name because Excel already knows we're working inside the sales table (although you could write it like =SUMX(Sales,Sales[Quantity]*RELATED(Products[Unit Price USD])) and that would be fine too)

[@RunaJokullsdottir]

same for me- i ended up with =SUMX(Sales, Sales[Quantity]*RELATED(Products[Unit Price USD]))

[@ajinkyashinde1778]

Please Introduce new course Azure Data Engineer 🙏

[@felekuseyoum5591]

I started Dat Analysis course can shareto me help fullvidio 🙏

[@Drizimar1]

My head hurts

[@BRichard312]

Excel is obsolete as a data analyst tool.

[@Chris-at-Maven]

False

[@BRichard312]

@@Chris-at-Maven Your response without qualification is analogous to your mindset in working with non-data analytic tools like Excel.

[@Chris-at-Maven]

@@BRichard312 We teach plenty of other analytics tools as well (SQL, Power BI, Tableau, Python), and they all play important roles when used in the right context and applied to the right problems. Writing off Excel as "obsolete" is incredibly short sighted.

[@BRichard312]

@@Chris-at-Maven Power BI is NOT an analytic tool it's a reporting tool and there is a big difference. Tableau requires the user to be more technical than Power BI in knowing how to construct ad-hoc dashboard visualizations. Python is a data analysis tool but it has poorly constructed plot visualizations. I would argue that R is the most comprehensive data analytic tool available today. It maintains extraordinary code economy and its plots provide the most compelling visualizations of ANY other data analytic tool that exists including but not limited to SAS, SPSS, MATLAB, etc.

[@Chris-at-Maven]

@@BRichard312 agree to disagree :)

[@laudkotey4382]

this was too fast i had to reduce the playback speed