What are the differences between Power Query, Power Pivot, Power BI?

Question

I want to make a contribution to the community by presenting these free tools for data analysis using Microsoft and, unfortunately, they are little known and exploited by many and can be very useful in our work of automation and creation of reports widely used today in Business Intelligence because it allows to analyze large volumes of data in our already known Microsoft Excel and is more intuitive for those who are not familiar with the VBA "macros".

I hope that over time the labels powerquery, powerbi and powerpivot are completed with questions for the use of languages M and DAX.

Note: I am learning to speak Portuguese, feel free to edit this post if you need.

Answer 1

Power Query Is a complement developed from the version of Excel 2010 and now by default in version 2016 as Get & Transform. It is part of Microsoft’s proposal for the development of ETL through its acronym in English (Extract, Transform, Load) (Extract, Transform, Upload), very consistent with the situation of the analysis of large volumes of information for any economic and social sector.

PQ Allows you to connect Excel with multiple sources of information and intuitively transform it into Query Editor. There is a considerable amount of tools to transform our data, these are stored step by step, giving us full control over the transformation process.

Additional features the language M or Power M for the most advanced is the ability to access line by line or step to edit the query and be able to incorporate the rest of the functions they are not in the basic environment of the query editor.

Power Pivot Is another complement as PQ developed since the 2010 version natively incorporated from the 2013 version. Here we can connect various information sources or with data already transformed in Power Query. Thanks to its integrated memory technology, you can store millions of records without major problems contrary to our limited Excel sheet.

He presents the function DAX that are designed to formulate and extract information from data cubes and allow us to create complex metrics and KPI indicators (Key Performance Indicators).

It allows us to create our data models in a relational way as we already know in Access and then view them using our traditional dynamic tables and charts or dynamically with Power View and Power Maps.

Powerbi is a software free for creating dashboards or dynamic reports, where we can also find the query editor Power Query and the formulas DAX, with these two powerful Powerbi tools allow us to analyze large volumes of information and the possibility to share it in our organization, locally or online, depending on the case.

Video tutorial