Power BI

Power BI Query Editor

 What is Power BI
Power bi is business analytic tools provide by Microsoft, it can connect to hundreds of
data sources. Simplify data preparation, visualization where end users can create
reports and dashboards by themselves without having to depended on IT(information
Technology) staff or database administrators.

Power bi is a cloud-based intelligence tool that provides visualizations with SSBI(Self –

Service Business Intelligence) tools. It is the online solution that enables us to share the
interactive reports and queries that was created using the Excel Bi Toolkit or Power BI
Desktop. It offers data warehouse capabilities including data preparation, data
discovery or interactive dashboards.
 ◦ This toolset can be predominantly divided

Power BI Toolsets into three main categories – desktop version,

cloud or web version and mobile version.
Power BI
Desktop Tool
◦ Power BI desktop is a
workhorse in this
toolset.
◦ You connect, extract,
transform datasets and
then create beautiful
reports using this
toolset.
◦ You can share these
reports offline or publish
to Power BI Service.
Those reports can then
be consumed directly on
web or across mobile
devices.
Four Stages of
Self-Service
Power BI
Power BI Desktop has
three major toolsets using
which you would be
creating various interactive
and dynamic dashboards.
◦ Get Data
◦ Data Modelling
◦ Visualization
What is Power Power Query is a part of Power BI. Just click on “Get Data” button

Query and you enter the Power Query world.

 ◦ Connect to several types of data sources (databases, files,
web pages, social media, APIs, cloud storage etc.)
◦ Bring and combine data (append, merge, join etc.) from
various places
With Power ◦ Derive new columns of data

Query you can ◦ Format, remove or reduce data

◦ Reshape data (transposing, grouping, pivoting, un-
pivoting and other creative ways)
◦ Write formulas to do advanced manipulation of data
◦ Publish refreshable datasets
What is Power
Pivot
◦ Power Pivot is a calculation engine for pivot
tables. You can use Power Pivot to model complex
data, set up relationships between tables, calculate
things to be show in value field area of Pivot tables /
pivot charts or visuals.
◦ Think of Power Pivot as a calculation layer between
your data and outputs. You can tell Power Pivot how
you want your calculations done thru a language called
as DAX and Power Pivot can give the answers. It is an
extremely fast & scalable software.
◦ Power Pivot is a part of Power BI. You can use
various features of Power Pivot from Modeling ribbon
and from data & relationship views.
 First one is to get data inside Power BI
desktop. You would be using Power Query
editor for this task.

Steps to Further, once you have data then you can

Create
create various data models through different
tables, then create relationship between those
tables and finally enter some Dax formulas to

Dashboard
calculate the desired result.

And now you would create visualization

based on DAX results which are later sliced
and diced using various dimensions.
QUERY EDITOR LEARNING CURVE
 https://powerbi.microsoft.com/en-us/downloads/

Get Power BI
Desktop
1. Go to Power BI site
2. Select Products >
Power BI Desktop
3. Download and save the
file.
4. Show Installation
5. Open the Power BI
Desktop
6. Show Get Data and
Power BI query editor
window.
Power Bi Desktop
Interface
 Views in Power Bi
Desktop
2
1 There are three views in Power Bi Desktop
1. Report View
2. Data View
3. Model (Relationship) View

3
 Report View allows you to create any number of
report pages with visualizations

You can move visualizations around, copy and

paste, merge etc.

Report Views A report will have at least one blank page to start

in Power Bi Pages appear in the navigator pane just to the left of

Desktop the canvas

To add new pages to your report, just click New

Page on the ribbon

To delete a page, click the X on the page’s tab at the

bottom of the Report View
 Data Grid- Shows the selected table and all columns
and rows in it. Columns hidden from Report View are
greyed out. You can right-click on a column for options

Modeling ribbon- Manage relationships, create

calculations, change data type, format, data category for

Report Views
a column

in Power Bi Formula bar- Enter DAX formulas for measures and

calculated columns

Desktop
Search- search for a table or column in your model

Fields list- Select a table or column to view in the data

grid
Relationship Views in Power Bi Desktop

Relationship View shows all the tables, columns and relationships in

your model

Applicable when your model has complex relationships between

many tables

Click on Relationships View to show your model in Relationship

Views
 A calculated column is a new column
that is created by defining a calculation

Create that transforms or combines two or

more elements of existing data

Calculated
Columns It enables to establish a relationship
between tables, when no unique fields
exist that can be used to establish a
relationship
 POWER BI
QUERY EDITOR
 Power Query Editor
Power query is a business intelligence tool available in Excel that allows you to import
data from many different sources and then clean, transform & reshape your data as
needed.

Add – in : This option will allow you to write queries from other sources or modify a
query script form the filter & Shape dialog. It’s an intuitive tool that lets you
manipulate, transform, consolidate, enrich, merge and do much more with your data.

Power Query is available as an addition to download & install for Excel 2010, 2013 and
will appear as an new tab in the ribbon labelled Power Query. In 2016 it was renamed
to Get & Transform and appears in the Data tab without the need to install any add-in.
 Power Query Editor
It allows you to set up a query once and then reuse it with a simple refresh. It is also pretty
powerful Power Query can import & clean millions of rows into the data model for analysis
after.

The best part about it is you don’t need to learn or use any code to do ant of it. The power
editor record all your transformation step by step and converts them into the M code like
similar to how the Macro recorder with VBA.

If you want to edit or write your M code you certainly can, but you definitely don’t need to.
CLEAN AND TRANSFORM DATA WITH THE
QUERY EDITOR
QUERY LAYOUT
QUERY EDITOR STAGES
Query Editor
Stages
◦ Power BI Query editor is an ETL (Extract,
Transform, Load) tool which can get data
from a wide variety of data sources (both
from your enterprise as well as from online
public data sources).

◦ Extract, transform, and load is the process of

moving data from different sources into a
centralized data warehouse.
 Power BI Query editor can extract and connect
Query Editor to almost any data source which is available
today whether it text files, excel files, access
Stages files, external databases, web, Hadoop, etc.
Query Editor
Stages
The real magic in Power Query happens in
transformation step i.e. when you get data
inside Power BI Query editor window. Power
BI Query editor would not load the entire data
population into its memory. It will load only
first 1000 rows of data with all columns /
fields. You would need to perform all the data
cleaning, enrichment, transformation on those
rows.
Query Editor
Stages

Power Query would then

automatically apply those
same steps to your entire
data set and load it to Power
BI Data Model.

You can also manually select

data from which query
should be loaded or not
loaded into data model.

Loading only selected data

will save your disk space
and also impact
performance of your reports.
We will look more on this in
later part of the course.
Query Editor
Stages
Now starting with next
slide we will start importing
data from different source
and look at the various
features in Power BI query
editor. For easy illustration
purpose I will be using
Excel as our primary data
source throughout this
course. However, you can
import data from other
source as well and can
apply the same features in
identical manner.
Click here for information related to importing data from other sources.
Getting the Data
◦ Click on Home Tab → Get Data
◦ Select one from the List showing
◦ Or For more resources click on More..
Pulling the Data
from many places
◦ Figure Showing Data import list from more
resources
Data Connector Parameters: Authentication phase:
Connectors
◦ Whenever you go through
the ‘Get Data’ window, you
end up selecting what is
known as a ‘Data
Connector’.
◦ Each connector provides
their own unique Navigation Table: Load or Transform phase:
experience. You can define
your own connector
experience when creating a
Power BI Custom
Connector, but the most
common experience that
you’ll find inside of a
Power BI Connector is the
following:
1. Connector
Parameters:
◦ When we first use a
connector, we usually get
this window that has what
it’s called a Connector
Parameters.
These are the set of
parameters that will define
the connection that will
happen against our data
source. In the image above,
it’s where we set the url for
our WooCommerce site and
the API version that the site
uses.
◦ We call a “singleton” a
connector that doesn’t require you
any type of Connector
Parameters
2.
Authentication
phase
◦ More often than not, you’ll
get straight to the
Authentication phase which
is standard for every single
connector. The only
difference is that some
connectors will only have
certain authentication
methods available while
others might have multiple
authentication methods
available.
◦ If your credentials are already
stored in your Power BI
Desktop, then you won’t get
prompted for this and you’ll go
straight to the next phase of
your connector.
3. Navigation
Table
◦ This is where you, as the
end user, will be able to
explore what the data
connector can offer you. In
some cases this navigator
window will offer you
multiple levels for you to
explore tables, functions
and other components that
the connector can offer
you.
◦ Some connectors will not display
this “Navigator” window and
will just take you straight into
the Power Query editor window.
This behavior is not
recommended for Power BI
Custom Connectors.
4. Load or
Transform
phase
◦ This is where you pick what
to do with the objects that
you’ve selected from the
Navigation table / Navigator
Window. You can choose to
either Load the data directly
to your data model or click on
the “Edit” / “Transform
Data” button to first work
with your data inside the
Power Query Editor window
before loading it to your data
model.
◦ It is highly recommended that you
always click the “Edit” /
“Transform Data” button to see
how the data looks like and if you
require any transformations or
cleaning to happen.
 LET’S LOOK AT
SOME EXAMPLES
OF HOW SOME
CONNECTORS
WORK
The Excel
Connector
◦ When we use the “Excel”, “Text/CSV”
connectors or other similar connectors that
work with a local file, we get taken straight to
a folder window to find the file that we want
to connect to.
◦ In the past, it used to take us to the
“Connector Parameters” window and it’d
have a “browse” button to find the file. Once
we chose the file, it would take us to the
Navigator window where we could see the
objects inside the Workbook which would
look like this:
◦ And it would follow the 4-step experience
that we described before.
The Folder
Connector
◦ If you choose the “Folder” Connector, then
the first window would actually be a simple
window that looks like a simple form:
The Folder
Connector
◦ After you input your folder path you’ll get
another window that will show you the output
of that connection:
◦ This is essentially a table, so the experience
from this connector is different to others
because it doesn’t expose a Navigation Table,
but rather just a simple table.
◦ With this table you can choose to either
“Combine & Transform Data” or simply
“Transform Data”, the first one being a
unique experience for this Connector
FEATURES WORTH REMEMBERING…