75 Awesome Power Query, Power Pivot, and Power BI Resources That Will Level Up Your Data Ninja Skills

In our journey to become data ninjas, it’s never a solo thing. Many people from all around the world contribute to our learning and growth. Looking back, we wished we had a go-to list of resources that could address our different needs at different points in time. So, we’ve compiled what we found helpful along our journey and put together this list to guide you along yours! You can also get this resource list as a pdf copy right in your inbox.

  1. Resources that help you make sense of the big picture and influence people to come on-board

  2. Blogs

  3. YouTube Channels, Playlists, and Webinars

  4. Books

  5. eBooks

  6. Online Courses

  7. Offline Courses & Workshops

  8. Forums

  9. List of Power Query M and Power Pivot DAX functions

  10. Other Resource Lists

 

1. Resources that help you make sense of the big picture and influence people to come on-board

Congratulations on discovering the magical realm of Power Query, Power Pivot and / or Power BI! Maybe you stumbled into this realm by accident. Perhaps you overheard someone talking about it.

Nonetheless, it must have dawned upon you how this magic can change your (Excel) life. Thing is, you’re bursting with excitement to share it with someone else. You want to get your team on-board. You need your supervisor’s green light.

You try telling them about it.

And then you realise, gosh, where do you even begin? How do you tell someone what Power Query, Power Pivot and / or Power BI is? More importantly, why should they care?

Somehow it just doesn’t come out… impressive. If only they could just get it!

So, first, you show them.

You could build something specific to your team using your data sets.

1.1. Sample Power Query, Power Pivot, and Power BI dashboards that you can use for a demo

Or you could make use of this automated Power Query and Power Pivot sample template to do a live demo. If you’re connected to the Internet, you could also use this interactive Power BI dashboard.

1.2. Videos that storytell your data problems and how Power Query and Power Pivot solve them

Perhaps you aren’t comfortable with using the sample dashboards above. Instead, you prefer to paint problem statements and give a sneak peak on how Power Query and Power Pivot can solve them. And because your boss and colleagues are really busy, you’ve got just a few minutes to get them hooked. Here are some really short and entertaining videos (most of them under 2 minutes!) you could share:

 
 
 
 
 
 
 
 
 
 
 
 
 
 

1.3. Videos that showcase how Power Query and Power Pivot are used with sample case studies

Now, someone gets interested. And being data folks, we’re one skeptical bunch. Give us more detailed evidence, or we won’t believe what we see. Here are some videos that showcase what Power Query and Power Pivot can do in greater detail:

 
 
 
 

1.4. Blog posts that help you wrap your head around “What exactly is Power Query / Pivot / BI?”, “How are they related?”, and “How do I get them?”

Great! You’ve really got your supervisor’s and teammates’ attention this time. Problem is, they’re not too sure how everything fits in and where to start. Actually, truth be told, even you struggle to place them. These blog posts lay out what these tools are, the scope of problems they are meant to solve, how they are related to each other, and how to get started.

Chandoo covers the basics with his well-written text and schematic diagrams. He addresses:

  • What is Power BI, Power Query, and Power Pivot;

  • How they are related to each other and Excel; and

  • How to get them.

Matt focuses on the challenges Excel users face with Pivot Tables and shares how Power Pivot smashes through those limitations.

  • Limitations of Pivot Tables;

  • What Power Pivot is not and is; and

  • Benefits of Power Pivot.

Doug shows you step-by-step how Power Query’s user-friendly interface gets your data cleaned without all the manual drudgery in normal Excel. Most of all, it’s a repeatable sequenced workflow. Think of all the time and pain you’ll save!

This 5-part blog + video tutorial series is for folks who want an in-depth side-by-side comparison of how the same business problem can be approached by using “normal” Excel formula vs. a combination of Power Query and Power Pivot. This series is based on a complete teardown of a Excel template which you can download and follow on. The first half of the series shows you how using “normal” Excel in a different way can already make a huge difference in automating dynamic time calculations. The second half shows how Power Query and Power Pivot is a cut above, easily propelling you into data ninja stardom. Useful in addressing “So how exactly is Power Query and Power Pivot different from ‘normal’ Excel?”

When you’re ready to get started, this blog post guides you with step-by-step instructions and screenshots on how to install the Power Query and Power Pivot add-ins for Excel. There is an accompanying video at the end of the post.

 
 

 

2. Blogs

“If I have seen further it is by standing on the shoulders of Giants.” - Isaac Newton

On our journey to become data ninjas, wise and knowledgeable masters are crucial in guiding us along the path. Their contribution to public knowledge through their blogs let us tap their brains and avoid pitfalls. You can subscribe to them for the latest blog posts and free e-book resources! They are also a great way to get updates and keep up with the fast-paced ever-changing technology behind Power Query, Power Pivot, and Power BI. Here are some of our favourites in alphabetical order:

For those of you a little further along in your journey, and are looking to improve performance, here are some helpful posts on avoiding pitfalls, best practices, and performance optimisation:

Gil’s series of pitfall posts is a must-read for aspiring data ninjas. This is about the battle scars he racked up and how we can avoid making some of these costly mistakes. He sets up those situations blow-by-blow, highlights where exactly things break down, and then shows you how you can avoid those pitfalls. We love the detailed screenshots!

Like Gil, this post by Matt could only have come about from his years of experience with DAX. He succinctly explains his 19 tips that cover naming conventions, loading and shaping data, modelling, and other advice. Immensely helpful!

This slightly more technical piece by Marco makes clear how to better use the available memory, especially if you’re using 32-bit Excel. You’ve probably been told / read somewhere before you should only bring into your models the data that you really really really need. This post explains exactly the impact of having unnecessary rows and columns on performance.

 

3. YouTube Channels, Playlists, and Webinars

To complement the blogs above, we recommend checking out video content on YouTube channels, playlists, and webinars. This list has a mix of “how-to” tutorials, best practices, latest tech updates, webinars, and live talk shows:

 

4. Books

We find that the most cost efficient, rigorous, and methodical method to build up those Power Query / Pivot / BI muscles are good ol’ books. Our favourites are listed below. They have great opening introductions that contextualise the data work we do and the environment we operate in - We don’t often come across such well-nuanced descriptive prose which we find ourselves nodding a lot to! The books are packed with highly applicable examples which we frequently come across in real life. We tend to reach for them from time to time as we build solutions for our consulting projects.

We’ve provided links to Amazon where you can buy hard copies or eBooks. Even if you don’t have a Kindle, you just need a Kindle app to download the eBooks. Works great across laptops and multiple mobile devices! One reason to get the eBook option is because you get new content for free if the authors decide to upgrade the book.

ATTENTION SINGAPORE RESIDENTS! You can also borrow the hard copies and / or eBooks available from the National Library. You’ll need an Overdrive account and the Overdrive app to download the eBooks on your mobile devices.

  • “M is for (Data) Monkey: A Guide to the M Language in Excel Power Query” [Ken Puls & Miguel Escobar]

[Available on Amazon] [Available at Singapore’s NLB]

This was what got us started on our Power Query journey. We’ve managed to ninja our way through 80% of the usual data cleaning, shaping, and preparation work that we do with the exercises in this book!

 
  • “Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016” [Rob Collie & Avichal Singh]

[Available on Amazon] [Available at Singapore’s NLB]

And this was what kicked off our Power Pivot journey. We liked the schematic diagrams that helped us understand abstract concepts, the many hands-on files we could tinker with while we worked through the book, and the easy-to-understand step-by-step explanation of DAX syntax when applied to case problems.


 
  • “PowerPivot Alchemy: Patterns and Techniques for Excel” [Rob Collie & Bill Jelen]

[Available on Amazon] [Available at Singapore’s NLB]

We recommend this book after you’ve gotten the hang of Power Pivot DAX basics. Start on this only after you’ve finished the previous book “Power Pivot and Power BI”. This book assumes you’ve got a certain level of grasp on DAX as it jumps right into very applicable real-world issues.


 

5. eBooks

Ok, so you’re just getting started but not sure if it’s worth your investment in the long-form books. You’d like some resources that are more bite-sized. Something a bit more structured than just blog posts to dip your toes into. Or maybe you’re just looking for variations of similar material to hone that data ninja leap. Here’s a list of FREE shorter-form eBooks and digital publications that can help:

This highly visual (130 illustrations!) how-to guide contains 35 data magic tricks over 84 pages and is accompanied by 6 downloadable cases across both Excel and Power BI applications. We enjoyed the very clearly laid out chapter structure - Briefing, How-To, and Summary. Pay particular attention to the “Recommendations” and “Personal comments from the authors” segments in the Summary. They contain “leverage” that really turbocharges your data wrangling skills in a really short span of time.


 
 
 

Reza has been diligently adding content to this eBook every single week. His 4th edition as of January 2019 contains more than 1,700 pages! It’s so huge that he now breaks it into five books. In his own words, “if you want to learn Power BI from ground zero to sky hero, you would need to read them all”. It’s got content from Level 100 to 400! If you’re wondering why he would publish all that material online for free, it’s because his take is that the traditional publishing process wouldn’t be able to keep up with the fast-paced changes in Power BI!

 

Alright, this isn’t an eBook. It’s an eCard. And a pretty darn nifty one at that. On the opposite end of the spectrum of Reza’s book, it’s this 8-page cheatsheet jam-packed with Power Pivot DAX tips and best practices. We recommend using this for a good round up after your initial forays into this magical realm to see if you’ve got your basics down pat.

 

6. Online Courses

If you can’t quite sit with a book, or need a more visually-rich environment to help you learn, here’s a list of well-structured online courses. Step-by-step, they incrementally build upon each chapter to get you from zero to hero.

This online course focuses on cleaning, reshaping, and combining your data with Power Query. It’s the crucial first step in your data ninja journey. Here’s what you get when you enroll:

    • [Approach] Self-paced learning. Hands-on with quizzes at the end of every module to test what you’ve learnt;

    • [Video] Over 13 hours of video training;

    • [eResource] Free eBook copy of their Amazon #1 best-selling book “M is for (Data) Monkey”, with first dibs to see the new-and-improved 2nd edition. Downloadable files and an example database;

    • [Support] Get your questions answered by experts via the discussion forum or helpdesk;

    • [Customer satisfaction] FREE 1-hour trial. Quarterly updates with new content every 16 weeks;

    • [Validity] 1-year access, reduced price for subsequent years.

  • Excel Power Query Course [My Online Training Hub]

Here’s another online course that focuses on Power Query for Excel. We like that the course page succinctly puts together many different elements to describe Power Query, its benefits, and connects with its target audience. Here’s what it comprises:

    • [Approach] Self-paced learning. Bite-sized and fast paced.

    • [Video] Over 6 hours of video training. Pricing plan has option to download videos;

    • [eResource] Bonus eBook of “M is for (Data) Monkey” by Miguel Escobar and Ken Puls. Downloadable workbooks and practice data;

    • [Support]

    • [Customer satisfaction] 30-day money back guarantee;

    • [Discount] 20% off with combination of 2 or more courses such as Power Pivot for Excel;

    • [Validity] 1-year.

  • Comprehensive Power Pivot Online Video Training Course [PowerPivotPro]

Once you’ve learnt how to clean, reshape, and combine your data at the Power Query Academy, build powerful data models and flexible measures with this Power Pivot online course. Here’s what you get when you register:

    • [Approach] Self-paced learning. Entertaining, easy-to-understand style, designed for Excel people, concepts demystified via clear animations and examples;

    • [Video] Over 20 hours of comprehensive video;

    • [eResource] Bonus eBook and guides;

    • [Support] Get your questions answered by experts via the comment feature;

    • [Discount] Bulk discounts for multiple attendees. Special offer 15% off if you bundle this with Comprehensive Power Query Online Video Training Course (where they partner with Miguel Escobar & Ken Puls);

    • [Validity] 1-year access.

Unlike the above two courses which are entirely self-paced, this course has a semester schedule. It also includes weekly live screen sharing Q&A sessions with the expert, Matt Allington. The focus here is on learning Power Pivot either in Excel or Power BI. Here’s what you get:

    • [Approach] Combination of self-paced remote learning using one of two eBooks + weekly video based training + weekly live screen sharing Q&A sessions. All these across 5-week semesters;

    • [Video] 5 hours of examples and demos to support complex topics in the eBooks. Recordings of live Q&A sessions will also be emailed for future reference;

    • [eResource] Free copies of both eBooks, “Supercharge Power BI” and “Supercharge Excel”;

    • [Support] Weekly live Q&A sessions;

    • [Customer satisfaction] FREE preview of some chapters;

    • [Discount] Get 10% off if you bundle this with Power Query for Excel and Power BI Online Training;

    • [Validity] 3-year access.

Another course that focuses solely on Power BI, Chandoo calls it a “play date” as he finds “working with data in Power BI feels like playing”. It’s an introduction to Power Query and Power Pivot within the Power BI environment. Do note that this Play Date runs in batches. If enrollments are closed, you can join the waiting list to get notified when it re-opens. Here’s what you get when you successfully enroll:

    • [Approach] Self-paced learning;

    • [Video] Over 8 hours;

    • [eResource] Full example workbooks and datasets;

    • [Customer satisfaction] 30-day money back guarantee;

    • [Discount] Team discounts available;

    • [Validity] 1-year access.

A different approach from the courses above, Excel School integrates Power Query and Power Pivot within a much larger, broader, and comprehensive set of content that unleashes the true potential of Excel. Here’s what you can expect:

    • [Approach] Self-paced learning. Covers a comprehensive range of Excel functions and formula grounded with practical real-world examples;

    • [Video] Over 20 hours of video content that can be downloaded (HD quality);

    • [eResource] Formula cheat sheet and shortcuts poster;

    • [Customer satisfaction] 30-day money back guarantee;

    • [Discount] $50 discount;

    • [Validity] 2-year access.

 

7. Offline Courses & Workshops

For the most interactive, engaging, and inspiring learning experiences, nothing beats in-person trainings. Whether you’ve got questions on the concepts, formula, functions, or just want to bounce possibilities, your trainer and fellow classmates can give you on-the-spot answers and feedback! Trade data war stories and learn from each other’s experiences over breaks and lunches too. If you find yourself getting distracted by tenthousanddifferentthings while self-learning via the earlier options, this will be the fastest, richest, and most focused way to get a solid start.

  • Australia

Power BI Training for the Business Analyst [Excelerator BI]

Chandoo Masterclass [Chandoo via PlumSolutions]

Mastering DAX Workshop | Data Modeling for Power BI [SQLBI]

  • Europe

Mastering DAX Workshop | Data Modeling for Power BI [SQLBI]

  • United States

Foundations: Power Pivot & Power BI [PowerPivotPro]

Level Up Series: Advanced DAX [PowerPivotPro]

Level Up Series: Power Query for Excel & Power BI [PowerPivotPro]

Mastering DAX Workshop | Data Modeling for Power BI [SQLBI]

  • Singapore

10x Your Speed: Data to Insights with Power Pivot [Lightdotlab]

10x Your Speed: Dirty to Clean Data with Power Query [Lightdotlab]

 

8. Forums

Here’s where the rubber hits the road. No matter how you much read, or learn through books, online and offline courses, it’s the real deal when you actually work on your own data models. For many useful fundamental concepts and principles, we believe the resources above will get you rather far. Heck, they also include gold nuggets of advice that got crystallised from much experience! However, when you’re working on that specific nitty-gritty that just somehow doesn’t seem to work, here’s when you reach out to the forums for some inspiration or help.

Power Query / Pivot / BI have been around for a while and there’s a significant repository of information you can do a search on. If there’s no close match to what you’re looking for, you can also post your questions. Be sure to give enough context, or include a sample data file (keep the structure, leave out sensitive and confidential data) so that the community can help you out.

 

9. List of Power Query M and Power Pivot DAX functions

Need a quick reminder of the syntax for Power Query’s M or Power Pivot’s DAX functions? Head on over to Microsoft’s official documentation for a refresher. When figuring the most effective functions to use in our consulting projects, we find it helpful to scroll through these lists to discover new functions that we don’t already know exist but think they might given existing adjacent functions.

 

10. Other Resource Lists

We hope you find this resource list useful to guide you on your Power Query / Power Pivot / Power BI data ninja journey - be it helping you introduce this awesome magical realm to influence more people to get on-board, wrapping your head around how these magic powers work, getting your fundamentals right with books, online and offline training courses, to continuous learning via expert blogs and forums! To round off, here’s a list of links that have similarly put together a collection of helpful resources:

We know this list is by no means comprehensive. If you’ve got a highly recommended resource to add to it, do let us know in the comments below!