Power Pivot is the self-service BI add-in for Microsoft Excel

Since 2010, with the creation of Power Pivot, Excel users were empowered to perform complex data analysis themselves. Users no longer depended on BI professionals for support, which was a game changer in the world of data analysis.

The primary expression language that Microsoft uses in Power Pivot is DAX (Data Analysis Expressions), although others can be used in specific situations. Again, as Microsoft explains, “DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.” Fortunately for those already familiar with Excel, DAX formulas will look familiar, since many of the formulas have a similar syntax (e.g., SUM, AVERAGE, TRUNC).

For clarity, the key benefits of using Power Pivot vs. basic Excel can be summarized as the following:

·        It lets you import and manipulate hundreds of millions of rows of data where Excel has a hard constraint of just over a million rows.

·        It allows you to import data from multiple sources into one single source workbook without having to create multiple source sheets that suffer from version control and transferability issues.

·        It lets you manipulate the imported data, analyze it, and draw conclusions without slowing down your computer to a snail’s pace.

·        It lets you visualize the data with PivotCharts and Power BI.