What are the differences between Power Query, Power Pivot, Power BI?
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 explored by many and can be very useful in our automation and reporting work widely used today in Business Intelligence because it allows you to analyze large volumes of data in our already familiar with the VBA "macros".
I hope that over time the labels powerquery, powerbi and PowerPivot are filled with questions for using the languages M and DAX.
Note: I am learning to speak Portuguese, feel free to edit this post if you need.
1 answers
Power Query is a Add-on developed from Excel 2010 version and now by default in 2016 version 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 information sources and turn them intuitively in the Query Editor . There are a considerable amount of tools to transform our data, these are stored step by step, giving us total control over the transformation process.
Displays the language M or Power m for the most advanced is the ability to access line by line or step by step to edit the query and be able to embed 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 sources of information 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 Excel sheet limited.
It presents the functions DAX which are developed to formulate and extract information from data cubes and allows us to create complex metrics and KPI (Key Performance Indicators) indicators.
It allows us to create our data models in a relational way as we already know in Access and then visualize them using our traditional pivot tables and graphs or dynamically with power View and Power Maps .
PowerBI is a free software for the creation of dynamic dashboards or 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 of sharing them in our organization, locally or online, depending on the case.
