Climbing From Excel to Power Query to Power Pivot to Power BI

March 29, 2022 - by Bill Jelen

What is this "Modern Excel" that everyone is talking about? If you are already using Excel for 20+ hours a week, how do you get up to speed and move from traditional Excel to Modern Excel?

First, let’s define what Rob Collie meant when he coined the term Modern Excel.

• At the time, Rob was talking about the 2010 invention of Power Pivot and the DAX formula language. Power Pivot is an alternative way to store data in Excel. Instead of visible in the grid, the data lives in the Data Model. The Data Model is stored in the Excel workbook, but it is stored very efficiently using a Vertipaq compression tool. This allows for more than a million rows in a table. You can also define relationships between Sheet1 and Sheet2, preventing the need for millions of VLOOKUPs.
• Pivot tables that are based on the Power Pivot Data Model allow for a new kind of calculated field called a Measure. The formula language for Measures is called Data Analysis eXpressions or DAX. At first, DAX looks similar to the regular formula language in Excel. But there are powerful differences. DAX can change the filter context of any cell in the pivot table, including the ability to temporarily unfilter. Normally, if you have a pivot table that is showing sales for March 17, that cell can only see sales from March 17. But using DAX, the March 17 cell could look outside of March 17 in order to calculate all sales from March 1 to March 17 (Month to Date Sales) or perhaps all sales from March 17 of last year (Year-over-year Sales) or even March 18 of last year so you are comparing Thursday sales to Thursday sales. DAX takes some time to learn.
• Power Query is a set of data-cleansing tools that can help you get poorly formed data loaded into Excel or directly into the Data Model. When you clean data using Power Query, Excel remembers the steps required to transform the data from the original state to the cleaned state. When you get new data, you can simply Refresh. The Power Query tools are found on the Data tab in Excel, in a group called Get & Transform Data. Don’t be fooled. These tools look like the same tools in older versions of Excel, but they are far more powerful.
• Power BI Desktop is a new application from Microsoft. It includes all of the above: Power Query, Power Pivot, and DAX and adds a great visualization layer on top. Dashboards built using Power BI can be consumed in a browser or on a tablet such as the iPad. If you touch the West region wedge of a pie chart, all of the other reports on the dashboard are instantly filtered to show only the West results. Power BI doesn’t look like Excel. But it allows an Exceller to produce slick-looking visualizations that will be a hit in the board room.

As an Excel pro, where do you begin your journey? For me, Power Query has the most bang for the buck. I am always dealing with bad data. Knowing how to use the tools in the Power Query editor has saved me from having to write VBA macros to solve problems. Power Query is easy to learn – you can produce great results in just a couple of hours of training.

Next is Power BI. Take your Excel data and display it on an interactive dashboard.

Ultimately, though, the C-Level execs who love your new dashboards will be asking for stats that are going to require you to learn some DAX formulas.

Title photo by Wim van 't Einde on Unsplash