In my previous blog, I talked about the introduction of Excel Power Query and how it eventually became a default feature of Excel 2016 but changed its name to ‘Get Data’. However, Get Data is only part of the story – there’s another application that goes with it called Power Pivot, it not only features in Excel, now called ‘Data Modelling’, but is also an integral part of Power BI.
In simple terms, ‘Get Data’ does what its name suggests, it gets data, or connects to a wide variety of data sources – these include data files like Excel through to databases whether on-premise or cloud based. Having sourced the data, ‘Get Data’ offers various tools for transforming the data in readiness for analysis. Once the preparation is complete, the data is loaded into a data model – this is where Power Pivot takes over.
A data model usually consists of one or more tables, for example data about products, customers and sales transactions. Tables are joined together by relationships; calculations are usually created that access the model and new calculated columns are created. If you are familiar with Power BI, you will know that Power Pivot isn’t mentioned, this is because Power BI is seen as a single application rather than two distinct parts (Power Query & Power Pivot) as in Excel. Irrespective of the names, the technology is the same across both applications.
When the data model is built, the user is free to start designing reports using the imported data and calculations based on the data model.
The graphic below shows how both applications connect to multiple data sources, model the data and then create reports and dashboards.
