Excel, Power BI, Power Query

Power BI Resources and Reading List

I’m frequently asked by friends and colleagues where to start learning Power BI, data modeling, and the rest. I’ve assembled a few things here to have a quick place with my favorite answers all in one spot. These resources are highly dependent upon my own learning style. I’m a technically inclined guy, so I actually prefer books over YouTube videos, Udemy series, and blog posts (despite my own blog on the topic). Reason being that there are a number of great books that dive very deeply into these topics. I find that blog posts and video series are often too surface level or don’t sufficiently address fundamental topics. There are several distinct areas to study and learn when it comes to Power BI so I’ve grouped them accordingly.

The Big Idea

If you are new to business intelligence and data analytics, then perhaps it’s worth recapping what we are attempting to achieve with Power BI. Simply put, we are trying to understand data to make better decisions and be better informed. This is easier said than done. Business data comes in all different shapes and sizes. You will often have multiple systems that store data about your business, but you need to form an answer which spans across the different systems and subject areas. Alternatively, you may have a single system, but the built in reporting features aren’t robust enough or you have outgrown them. Power BI lets you reach into any kind of data and build powerful data models to answer these questions.

Gathering and Shaping Data

As mentioned above, you’ll need to be able to get data from different places and set it up in a way that’s useful. Here are some typical data sources that I’ve seen or worked with as a financial analyst.

  • SQL Server that stores data for a business application
  • A reporting package that you already have (SSRS, SSAS, Business Objects, etc). This usually has an “Export to Excel” option that you already abuse on a regular basis.
  • Web application with an export option or API access (Smartsheets, Big Time, Zoho, etc)
  • An Excel spreadsheet manually maintained by users with important data
  • Access database

The software for connecting and extracting all of this data is called Power Query. You can write these data extraction queries on your desktop using Excel or Power BI Desktop. If you have a Power BI pro or premium license then I recommend using Power BI dataflows in the web browser. It’s just Power Query, but it’s online and it’s awesome.

Recommended reading:

  • Collect, Combine, and Transform by Gil Raviv – This book goes very in depth on the Power Query language, best practices, and techniques. It starts from an extremely basic level, but it builds to very complex methods
  • The Microsoft Power Query Language Reference – This is Microsoft’s official documentation on Power Query. It’s very dry and sometimes very lacking in the depth of descriptions, but once you get up to speed on the language it’s the quickest place to go for a reminder on function parameters

Modeling Data

Once you’ve gathered and imported data from a bunch of different places, you’ll need to “model” it. Modeling refers to the techniques and methods for organizing the data into a way that is performant and flexible enough to answer questions and build reports. This is done in Excel’s Power Pivot add in or Power BI’s Modeling view. This may sound intimidating if you’ve never done it before, but if you are comfortable with VLOOKUP/XLOOKUP and pivot tables in Excel, then you are ready for data modeling.

Recommended reading:

  • Analyzing Data with Power BI and Power Pivot by Marco Russo and Alberto Ferrari -This critical book starts at the foundation of a good but basic data model and expands to much more complex topics. It is refreshingly light on DAX (the data model formula language) because it focuses so singularly on data modeling techniques. The two authors will appear frequently throughout the rest of this post.
  • DAX Patterns by Marco Russo and Alberto Ferrari – Once you are familiar with the general concepts and techniques of modeling, this book give examples of specific modeling scenarios and how to approach them. It’s a bit more of a reference book for when you need it rather than a straight read.
  • Definitive Guide to DAX (Second Edition) by Marco Russo and Alberto Ferrari – DAX is the name of the formula language that exists within the Power BI data model. It is used for defining calculations to be used within reports. This is the ultimate DAX book, there is no other resource which is more exhaustive and detailed. It is indeed definitive. It’s over 600 pages, so again a bit of a reference book, but the concepts continue to build on themselves through each chapter, so it helps to read in order.

Visualizing and Delivering Data

After modeling the data, it’s time to present and deliver it. The data needs to be digestible and useful to actual people. This is often a very challenging topic for nerds like myself. This is also the place where Excel and Power BI diverge significantly in their capabilities and feature sets. Excel has an add in called Power View which feels a bit like Power BI, but it’s no longer supported and somewhat clunky. The best way to visualize and deliver data in Excel is with pivot tables, charts, and CUBE formulas in the trusty Excel grid. In Power BI, there are tons of cool and interactive charts that are all accessible via web browser or phone, not something Excel is good at.

Recommended reading:

  • Please suggest something! This is admittedly the area where I have the least experience. I’ve seen several books recommended, but haven’t sufficiently read them to be able to recommend. I’ll update this post as I find good resources.

Other Resources

I think the above books are great for diving deeply into the subjects, but if you prefer video content or need a more basic intro, check out some of these.

  • Guy In A Cube YouTube Channel – The two guys who run this channel make really great videos on just about every topic in Power BI. They are shorter (5 – 15 minutes) and far less technical than the above books, but great for introducing the topics. They’ll have a video on any question you have like What is Power BI, Data modeling best practices, and some visual design techniques.
  • sqlbi.com – This is the website of Marco and Alberto Ferrari who wrote several of the books listed above. There is a tremendous amount of information here in the form of blog posts, courses, and live training sessions.
  • P3 Adaptive Blog and the Raw Data Podcast – Rob Collie was one of the early Power BI bloggers and you can’t go wrong with any of his content. He has several books which I don’t explicitly recommend because I haven’t read them, but he is a very interesting and entertaining guy so I’m sure the books are worth a read. His podcast is non-technical and a great way to keep up with trends in the data analytics industry. This presentation from Rob has been pretty influential on my thinking around Power BI and data analytics.

I hope these resources will be helpful. Data analytics is a fascinating and very deep subject area and I wonder whether it is possible to truly master it. I’m certainly enjoying the journey of learning. Connect with me if you want to talk more!

This website has no monetization strategy and none of the links are affiliate referrals.