Saturday, December 9, 2017

Power BI #3: The Query Editor

When you launch the Power BI Desktop, the start up screen you get has Get Data on the top left side.

The Get Data is the your first window into the Query Editor. If you are familiar with Excel, the Query Editor is the exact replica of PowerQuery. 

In Power BI, it is your main data manipulation and data cleaning tool. Once you connect to the data you want to analyse, it is good to go to the Query Editor to examine the data and, if the data needs some cleaning or transformation, do all that transformation in the Query Editor.

Clicking on Edit, when done connecting to the data, takes you to the Query Editor, and I recommend you always use Edit rather than Load which brings in the entire data without allowing for preview and modification/transformation.

Below is what the Query Editor looks like and it always opens as a separate window from the main Power BI window.

The Query Editor can be divided into four functional sections.

1. Menu section
2. Queries section
3. Data Preview section, and
4. Query Settings section.

The Menu section is more like the control panel housing all the tools you will need for most of your data cleaning and data transformation processes. In the end, it is a section you will have to be very proficient at and we will do a lot of practical demonstration of real world analysis that involves using this section.

The Queries section mainly lists all the data sources you are connected to. Right-clicking on any of the data source gives you some very useful set of options.

The Data Preview section shows a preview of the data selected in the Queries section. This gives the Query Editor some advantages over loading the data directly into the Data model, especially in a case of a large data set that would take too many system resources and time to load. By loading just a preview, one can get working immediately on the data and even set filters and formulas to pull in just the segment of the data set that is needed rather than pulling in the entire data set. It also has some useful features — like filter, rename, delete, replace errors and others.

The Query Settings serves as a very interactive and feature-rich audit trail. It allows you to see all the transformation steps carried out in applied order. You can modify any step and re-order the steps if you want.

In future chapters we will do some real world analysis that will help us further dive into the Query Editor and see its practical usefulness.