POWER BI

INTRODUCTION
• Power BI is a technology-driven business intelligence tool provided by
Microsoft for analyzing and visualizing raw data to present actionable
information.
• It combines business analytics, data visualization, and best practices that
help an organization to make data-driven decisions.
• In February 2019, Gartner confirmed Microsoft as Leader in the "2019 Gartner
Magic Quadrant for Analytics and Business Intelligence Platform" as a result
of the capabilities of the Power BI platform.
WHY POWER BI?
• Access to Volumes of Data from Multiple Sources
• Interactive UI/UX Features
• Exceptional Excel Integration
• Accelerate Big Data Preparation with Azure
• Turn Insights into Action
• Real-time Stream Analytics
ADVANTAGES OF POWER BI
• User-friendly interface

• Data integration

• Customizable dashboards

• Real-time data

• Collaboration
 DISADVANTAGES OF POWER BI
• Limited data processing capabilities

• Limited customization options

• Cost
POWER BI ARCHITECTURE
Power BI is a business platform that includes several technologies to work
together. It delivers outstanding business intelligence solutions.

Power BI Architecture contains four steps.

• Data Integration
• Data Transforming
• Report & Publish
• Creating Dashboard
 DATA INTEGRATION:
• Data is extracted from different sources which can be different servers or
databases.
• The data from various sources can be in different types and formats.
• If you import the file into the Power BI, it compresses the data sets up to 1GB,
and it uses a direct query if the compressed data sets exceed more than
1GB.
• Then the data is integrated into a standard format and stored at a place
called a staging area.
• There are two choices for big data sets. They are as follows.
• Azure Analytics Services
• Power BI premium
 DATA TRANSFORMING:
• Integrated data is not ready to visualize data because the data should be
transformed.

• To transform the data, it should be cleaned or pre-processed.

• For example, redundant or missing values are removed from the data sets.

• After data is pre-processed or cleaned, business rules are applied to transform the
data.

• After processing the data, it is loaded into the data warehouse.

REPORT & PUBLISH:
• After sourcing and cleaning the data, you can create the reports.

• Reports are the visualization of the data in the form of slicers, graphs, and
charts.

• Power BI offers a lot of custom visualization to create the reports.

• After creating reports, you can publish them to power bi services and also
publish them to an on-premise power bi server.
CREATING DASHBOARDS:
• You can create dashboards after publishing reports to Power BI services, by
holding the individual elements.

• The visual retains the filter when the report is holding the individual elements
to save the report.

• Pinning the live report page allows the dashboard users to interact with the
visual by selecting slicers and filters.
COMPONENTS OF POWER BI
ARCHITECTURE
DATA SOURCES
Data Sources supported in Power BI

• File Types: Power BI supports XML, txt/CSV, Excel, JSON, and Share point folder type
files.

• Database: It supports SQL Server Analysis Services Database, SAP HANA Database,
SQL Server Database, SAP Business Warehouse server, Access Database, Google
BigQuery (Beta), Amazon Redshift, Snowflake, Impala, Oracle Database, IBM Informix
database (Beta), Teradata Database, MySQL Database, IBM Netezza (Beta), Sybase
Database, PostgreSQL Database.

• Azure: Azure SQL Data Warehouse, Azure Blob Storage, Azure Analysis Services
database (Beta), Azure SQL Database, Azure Data Lake Store, Azure Table Storage,
Azure HDInsight (HDFS), Azure Cosmos DB (Beta), Azure HDInsight Spark (Beta).

• Online Services: Power BI service, Dynamics 365 (online), Microsoft Exchange Online,
Common Data Service (Beta), SharePoint Online List, Visual Studio Team Services
(Beta), Dynamics 365 for Financials (Beta), Microsoft Azure Consumption Insights
(Beta), Salesforce Objects, Salesforce Reports, Google Analytics, Dynamics 365 for
Customer Insights (Beta), GitHub (Beta), appFigures (Beta), comScore Digital Analytix
(Beta), Facebook, Kusto (Beta), Planview Enterprise (Beta), MailChimp (Beta),
Mixpanel (Beta), QuickBooks Online, Projectplace (Beta).

• Other Services: Hadoop File (HDFS), Vertica (Beta), Web, OData Feed, SharePoint List,
Microsoft Exchange, Active Directory, R Script, ODBC, Spark (Beta), Blank Query, OLE
DB.
 POWER BI DESKTOP
• It is free software that enables you to connect, transform and visualize the
data on your desktop.

• You can connect to various data sources with the help of Power BI Desktop
and combine the data into a data model.

• This data model allows you to create a collection of images and graphics
that make you share the information within the organization as records.

• The majority of the users who work on Business Intelligence projects use
Power BI Desktop to create and share their reports with others.
POWER BI SERVICE
• Power BI Service is an On-Cloud service with a web-based platform and used to
share and publish the reports made on Power BI Desktop.
• It collaborates the data with other users and creates dashboards.
• Power BI Service is also called “Power BI Workspace”, “Power BI Web Portal”, and
“Power BI Site”.
• Power BI Service offers wonderful features like alerts and natural language Q&A.

• It is available in three versions. They are as follows:

• Premium version
• Pro version
• Free version
POWER BI REPORT SERVER
• Power BI Report Server is similar to the Power BI Service.
• It is an On-Premises server platform.
• Using Power BI Report Server, organizations can secure their data.
• It enables the users to create reports and dashboards and allows you to
share the reports with other users or organizations with proper security
protocols.
• To use this service, you need to have a Power BI premium license.
 POWER BI GATEWAY
• Power BI Gateway is used to maintain fresh information by connecting to
your on-site data sources without transferring the data.
• It provides secure data and allows you to transfer the data between
Microsoft cloud services and on-premise services.
• Microsoft cloud services include PowerApps, Power BI, Azure Analysis
Services, Microsoft Flow, and Azure logic apps.
• By using a gateway, organizations can maintain the databases and other
data sources securely in cloud services.
POWER BI MOBILE APPS
Using Power BI Mobile Apps, you can stay connected with on-
premises data from anywhere.

Power BI apps are available for iOS, Windows, and Android

platforms.
POWER BI EMBEDDED
• Power BI Embedded is an On-premises service in Azure.

• It offers APIs for embedding the reports and dashboards

into custom applications.
POWER BI ARCHITECTURE - WORKING
 ON-PREMISE
• Power BI Desktop is accomplished with the authenticating, development
and publishing tools.
• We can transfer the data from data sources to Power BI Desktop.
• It also allows users to create and publish reports on the Power BI Report
Server or Power BI Service.
• Power BI Publisher allows you to publish the Excel workbooks to the Power BI
Report Server.
• Report Publisher and SQL server Data tools help in creating the KPIs,
datasets, paginated reports, mobile reports, etc.
• All kinds of reports are published at the Power BI Report Server, and from
there, reports are distributed to the end-users.
 ON-CLOUD:
• Power BI Gateway is the essential component in the Power BI architecture.
• The Power BI Gateway acts as a bridge or secure channel to transfer the
data from On-premise data to On-cloud data sources or apps.
• Cloud side architecture consists of a lot of components including Power
suite having datasets, dashboards, reports, Power BI Premium, Power BI
Embedded, etc.
• Users can embed the dashboards, reports into applications, SharePoint,
Teams, etc.
• There are Cloud data sources and they are connected to the Power BI tools.
 POWER QUERY
• A tool used to manipulate and transform data for business intelligence
projects.
• Power Query is an invaluable tool for any analyst working in Excel, saving
users hours of time, reducing manual errors, and allowing users to source
data from a central source of truth.
• Power Query remembers the user’s data transformation steps, effectively
automating lengthy and manual processes.
• Common use cases are to modify the layout of data, connected to central
data sources, merge related tables, or combine multiple files.
BENEFITS OF POWER QUERY
• Transforming data
• Repeatable steps
• Combining multiple tables
• Combining multiple files
• Connection to central data sources
EXTRACTING DATA FROM DATA SOURCES
In Power Query Select File > Home > Data Source Settings.
TRANSFORMING DATA
Load the data

– Rename the query

– Rename columns
– Remove rows and columns
– Remove rows with filters
– Remove duplicates
– Merge columns
– Replace values
– Format text
– Change data type
– Add a custom column
LOAD THE DATA
Before we can get started shaping and transforming our data we need to
either upload it or connect to the data source.
Power BI supports many databases and has connectors to support platforms
like Google Analytics and Salesforce.
1 – RENAME THE QUERY
• The query can be renamed in the
Query Settings pane on the right-
hand side of the canvas.
• If you’re loading in multiple
datasets from different sources
using a descriptive name makes it
easy to find what you’re looking for.

• This is even more important when

you are loading data from
a database.
• If you have datasets named
‘Query1’, ‘Query2’, and ‘Query3’
you and your end-user are going to
have a tough time finding what
they’re looking for.
 2 – RENAME COLUMNS()
• By right-clicking the column
header you can rename your
column to something that’s more
user friendly or descriptive.
• Like a descriptive name for a
dataset, a descriptive column
name makes it easier to find what
you’re looking for.
• This is especially important when
loading datasets with technical
names for columns.
• Have some empathy for your end-
user and they’re more likely to
trust your data.
3 – REMOVE ROWS AND COLUMNS
• You can find options to remove
rows and columns under the
‘Home’ tab on the ribbon.
• There are options to remove a
single row or column or remove
all but your selected row or
column.
• By removing unnecessary
columns it is easier for our end
users to get to what they need.
• Scrolling through unnecessary
columns is especially frustrating if
you have multiple datasets to
search through.
4 – REMOVE ROWS WITH FILTERS
• We can remove specific rows that
aren’t needed in our dataset using a
filter.
• The menu can help remove columns
that are empty, start or end with a
specific value.
• Just like the other steps to clean the
dataset, this will make it easier to
navigate for our end-user and help
them answer their questions quicker.
5 – REMOVE DUPLICATES
• By right-clicking a column and
selecting ‘remove duplicates’
we can remove any
duplicates in the dataset.
• This is useful when loading in
an unfamiliar dataset that
hasn’t come from a trusted
source especially if the
dataset is large.
• This could also be done in a
database but if you don’t
have permission to load in
data this may not be an
option for you.
 6 – MERGE COLUMNS
• By right-clicking a column and
selecting ‘merge columns’ we can
merge columns using a separator
and renaming it to something useful.

• This is useful for merging a First and

Last Name column, or columns that
would be better together.

• This can also be done with a formula

but right-clicking makes this task
quicker.
 7 – REPLACE VALUES
• You can find the replace values
option on the ‘Transform’ tab of the
ribbon.
• The advanced menu gives options
to insert special characters or
match the entire cell contents.
• Datasets may have been loaded
with typos that need cleaning up or
values that aren’t as descriptive as
they could be.
• This is especially useful if product
codes or jargon have been
introduced and plain English would
make it easier for end-users to
understand.
 8 – FORMAT TEXT
• Formatting options can be
found in the ‘Transform’ tab of
the ribbon and can be used to
trim, clean, and change the
case of text.

• Cleansing text fields make it

easier to read and perform
further transformations.

• The ‘Trim’ option is particularly

useful to remove any leading or
trailing spaces.
 9 – CHANGE DATA TYPES
• Clicking on the icon next to the
column header shows the data
type transformation options.
• This is a quick way to change the
data type if it has been loaded
incorrectly.
• Changing a number that has
been loaded as text means we
can start performing calculations.

• Making sure dates are stored as

dates means we can use these
as filters in dashboards.
 10 – ADD A CUSTOM COLUMN
• The custom column option can be
found on the ‘Add column’ tab of
the ribbon.
• A column can be created and
named here using a formula to
build the logic.
• This can be useful to add
calculations on existing columns,
adding text to an existing value, or
displaying part of a date.
• Just remember to check the data
type before you start trying to add
values together.
 DATA TYPES IN POWER QUERY
• Text data type
• Boolean data type
• Decimal data type
• Fixed Decimal Number data type
• Whole number data type
• Percentage Data type
• *Date/Time data type
• *Date data type
• *Time data type
• *Date/ Time/ Timezone
• *Duration data type
• *Binary data type
• *Any data type
FILTERS IN POWER QUERY
• Report-level filters
• that affect all of the data in the
report, regardless of what you're
looking at. Think of them as universal
filters.

• Page-level filters
• only filter the data on a given page,
which makes them useful for
creating pages that focus on
particular subsets of your data.

• Visual-level filters
• only filter the data on a given visual,
whether that's a table, chart, card,
slicer, etc.
FILTERING MODES
Basic Filtering Advanced Filtering

• In Basic Filtering, you are given • With Advanced Filtering, you

a list of values which is won't see a list of values to
scrollable and searchable. choose from, but you can use
• To search for a value, simply rules to determine a range of
type a keyword or identifier into values the report will return.
the search box, and the list of • For example, you can tell the
available values will report to show all Transactions
automatically update based on
the search criteria you entered. with a transaction amount
greater than or equal to $10,000.
• You can then select one or • After determining what rules you
multiple entries from the list
using the white checkboxes to want to use, click the "Apply
the left of each entry. filter" button on the filter card
and the report will recalculate.
FILTERING MODES
 MERGE QUERIES
A merge query creates a new query from two existing queries.
One query result contains all columns from a primary table, with one column
serving as a single column containing a relationship to a secondary table.
The related table contains all rows that match each row from a primary table
based on a common column value.
An Expand operation adds columns from a related table into a primary table.

There are two types of merge operations:

• Inline Merge You merge data into your existing query until you reach a
final result. The result is a new step at the end of the current query.
• Intermediate Merge You create a new query for each merge operation.
HOW TO PERFORM A MERGE OPERATION?
You need at least two queries that can be merged and that have at least one or
more columns to match in a join operation. They can come from different types of
external data sources. The following example uses Products and Total Sales.
1. To open a query, locate one previously loaded from the Power Query Editor,
select a cell in the data, and then select Query > Edit.
2. Select Home > Merge Queries. The default action is to do an inline merge. To do
an intermediate merge, select the arrow next to the command, and then
select Merge Queries as New.
The Merge dialog box appears.
3. Select the primary table from the first drop-down list, and then select a join
column by selecting the column header.
4. Select the related table from the next drop-down list, and then select a
matching column by selecting the column header.
CONTINUE….
Ensure that you select the
same number of columns to
match in the preview of the
primary and related or
secondary tables. Column
comparison is based on the
order of selection in each
table. Matching columns must
be the same data type, such
as Text or Number. You can
also select multiple columns
to merge.
CONTINUE…
5. After you select columns from a primary table and related table, Power
Query displays the number of matches from a top set of rows. This action
validates whether the Merge operation was correct or whether you need to
make changes to get the results you want. You can either select different
tables or columns.
6. The default join operation is an inner join, but from the Join Kind drop down
list, you can select the join operations.
7. To include only those rows from the primary table that match the related
table, select Only include matching rows. Otherwise, all rows from the primary
table are included in the resulting query.
8. Select OK.
RESULT
APPEND QUERIES
• An append query creates a new query that contains all rows from a
first query followed by all rows from a second query. This operation
requires at least two queries. These queries can also be based on
different external data sources.
• You can perform two types of append operations.
• With an inline append, you append data to your existing query
until you reach a final result. The result is a new step at the end of
the current query.
• With an intermediate append, you create a new query for each
append operation.
CONTINUE…
1. To open a query, locate one previously loaded from the Power Query
Editor, select a cell in the data, and then select Query > Edit.
2. Select Home > Append Queries. The default action is to do an inline
append. To do an intermediate append, select the arrow next to the
command, and then select Append Queries as New.

The Append dialog box appears.

CONTINUE…
3. Decide the number of tables you want to append:
• Select Two tables, and then select the second table in the drop
down list box to append.
• Select Three or more tables. From the Available tables box, add
the tables you want to append to the Tables to append. Use the
arrows on the right of that box to change sequence.
4. Select OK.
TYPES OF JOIN OPERATION
LEFT OUTER (ALL FROM FIRST,
MATCHING FROM SECOND)
RIGHT OUTER (ALL ROWS FROM
SECOND, MATCHING FROM FIRST)
FULL OUTER (ALL ROWS FROM BOTH)
INNER (ONLY MATCHING ROWS)
LEFT ANTI (ROWS ONLY IN FIRST)
RIGHT ANTI (ROWS ONLY IN SECOND)