Data Management & Visualization

(DMV)

Module 4
Data Visualization using PowerBi
Index

Lecture
Introduction to PowerBi 4
1
Lecture Loading data from various sources, Querying Databases and
16
2 Transformations on Databases
Lecture Creating Reports and Dashboard 32
3
Lecture Connection to various data sources, summarization 38
4
Lecture PowerBI DAX and Modeling
5
Lecture Mobile Dashboards, Publish Reports, Share Reports
6

2
Lecture 1

Introduction to PowerBi
What is Business Intelligence?

• Business Intelligence is the talk of a new changing and growing world that can
be defined as a set of concepts and methodologies to improve decision-making
in business through the use of facts and fact-based systems.
• The Goal of Business Intelligence is to improve decision-making in business
ideas and analysis.
• Business Intelligence is not just a concept it’s a group of concepts and
methodologies. Business Intelligence uses analytics and gut feelings for
making decisions.

4
What is Business Intelligence?

• Process Used in Business Intelligence: BI(Business Intelligence) uses a set of

processes, technologies, and tools (such as Informatica/IBM) to transform raw
data into meaningful information and then transform information to provide
knowledge. Then afterward some beneficial insights can be extracted manually
and by some software then the decision-makers can make an impactful
decision on the basis of insights.

5
 Features of Business Intelligence

• Business Intelligence about provides accurate information in the right and

ethical format to the decision-makers of the organization. Some Important
features of Business Intelligence are:
 Fact-based decision making.
 360 degrees perspective on your business.
 Virtual team members are on the same page.
 Measurement for creating KPI (Key Performance Indicators) on the basis of
historic data fed into the system.
 Identify the benchmark and then set the benchmarks for different processes.
 Business Intelligence systems can use to identify market trends and also to spot
business problems that need to be identified and solved.
 Business Intelligence helps in data visualization will increase the quality of data
and then also increases the quality of decision making.
 Business Intelligence systems can be used by large enterprises, and
organizations along with Small and Medium Enterprises, because it is quite
affordable.

6
Applications of Business Intelligence

• In Decision Making of the company by decision-makers of the organizations.

• In Data Mining while extracting knowledge.
• In Operational Analytics and operational management.
• In Predictive Analytics.
• In Prescriptive Analytics.
• Making Structured data from unstructured data.
• In Decision Support System.
• In Executive Information System (EIS).

7
What is PowerBI?

• Power BI is a collection of software services, apps, and connectors that work

together to turn your unrelated sources of data into coherent, visually immersive,
and interactive insights.
• Your data might be an Excel spreadsheet, or a collection of cloud-based and on-
premises hybrid data warehouses.
• Power BI lets you easily connect to your data sources, visualize and discover what's
important, and share that with anyone or everyone you want.
• Late in 2010, the initial concept for Power BI was created, and “Project Crescent”
was published the following year. The platform’s moniker was modified to “Power
BI” in 2013. (where “BI” stands for “Business Intelligence”).
• Initially, the Power Query, Power Pivot, and Power View add-ins for Excel served as
the foundation for the product.

8
Parts of PowerBI

• Power BI consists of several elements that all work together, starting with
these three basics:
 A Windows desktop application called Power BI Desktop.
 An online software as a service (SaaS) service called the Power BI
service.
 Power BI Mobile apps for Windows, iOS, and Android devices.

9
Parts of PowerBI

• These three elements—Power BI Desktop, the service, and the mobile apps—are
designed to let you create, share, and consume business insights in the way that serves
you and your role most effectively.

10
Parts of PowerBI

• Beyond those three, Power BI also features two other elements:

 Power BI Report Builder, for creating paginated reports to share in the Power
BI service.
 Power BI Report Server, an on-premises report server where you can publish
your Power BI reports, after creating them in Power BI Desktop.

11
What does PowerBI do?

• Power BI is a business analytics service that delivers insights to enable fast,

informed decisions.

Transform data into stunning Visually explore and analyze

visuals and share them with data—on-premises and in
colleagues on any device. the cloud—all in one view.

Scale across your

Collaborate on and share organization with built-in
customized dashboards and governance and security.
interactive reports.

12
Features of PowerBI

• Data Visualization: Power BI is used by organizations to create interactive

reports and dashboards that visualize data in various ways, including charts,
graphs, and maps.
• Collaboration: Power BI allows multiple users to work on the same report or
dashboard at the same time. This makes it easy for teams to collaborate, share
data and share insights.
• Data Transformation: Power BI can transform raw data into useful information
that is a more usable format by cleaning and manipulating data. This allows
businesses to work with clean, accurate data that is easy to analyze.
• Integration: Power BI integrates with a wide range of other Microsoft products
or applications like Excel, SharePoint, and Teams. It helps in the workflow of
the projects.
• APIs for integration. This feature provides developers with sample code and
APIs for embedding the Power BI dashboard in other software products.

13
Visualizations of Data - How does visualizing data help
users?

• Visualizations allow data to be

represented in different ways, leading to
insights into data relationships that may
not be easily seen.
• Power BI allows users to create and adjust
visualizations based on their own needs as
they look at data.

• Users will be able to look at data from

different perspectives and find insights into
data relationships that help them make
better informed decisions.

14
Steps of Data Sourcing to Creation of Reports and
Dashboards

The whole process of data sourcing to the creation of reports and dashboards
consists of four basic steps.

1. Data Sourcing in Power BI: Power BI offers a versatile range of data sources,
including cloud-based online services and local files. While there's a 1 GB limit on
importing data from online services, Power BI supports various sources such as
Excel, Text/CSV, XML, JSON, Oracle Database, and Azure SQL Database.

2. Data Transformation in Power BI: Before visualizing the data, a crucial step
involves cleaning and pre-processing. This includes eliminating missing values and
irrelevant data from rows and columns. Adhering to specific rules, datasets are
transformed and loaded into the warehouse for further analysis.

15
Steps of Data Sourcing to Creation of Reports and
Dashboards

3. Report Development in Power BI: Once data is cleaned and transformed,

reports are crafted based on specific requirements. These reports are essentially
data visualizations that incorporate different filters and constraints. The visual
representations can take the form of graphs, pie charts, and other graphical
elements.

4. Dashboard Creation in Power BI: Power BI dashboards are built by pinning

independent elements from live reports. This process occurs after publishing the
report to the Power BI service. The saved reports retain their filter settings,
allowing users to create dynamic dashboards with real-time data insights.

16
Fundamental Components of PowerBI

• The fundamental components of Power BI are decomposed into the entire

Power BI framework. The following are the fundamental parts that make
up Power BI’s overall operation:
1. Visualizations
2. Datasets
3. Reports
4. Dashboard
5. Tiles
• While connecting to Data Sources, we can choose from Excel files, Power BI
datasets or dataflows, SQL servers, Web Data, csv files, and many more. We
can also use Power Query to transform data in Power BI. Data from various
sources can also be combined. We can also clean and transpose data in
Power BI.

17
Lecture 2

Loading data from various sources, Querying

Databases and Transformations on Databases
Power BI – Data Sources and its type

• Power BI supports large range of data sources. You can click Get data and it
shows you all the available data connections. It allows you to connect to
different flat files, SQL database, and Azure cloud or even web platforms such
as Facebook, Google Analytics, and Salesforce objects. It also includes ODBC
connection to connect to other ODBC data sources, which are not listed.
 Flat Files
 SQL Database
 OData Feed
 Blank Query
 Azure Cloud platform
 Online Services
 Blank Query
 Other data sources such as Hadoop, Exchange, or Active Directory

19
Loading Data from various sources

• To get data in Power BI desktop, you need to click the Get data option in the
main screen. It shows you the most common data sources first. Then, click the
More option to see a full list of available data sources.

20
Loading Data from various sources - FILE

• When you click File, it

shows you all flat file
types supported in
Power BI desktop. To
connect to any file
type, select the file
type from the list and
click Connect. You
have to provide the
location of the file.

21
Loading Data from various sources - Database

• When you click the Database

option, it shows a list of all
the database connections
that you can connect to.
• To connect to any database,
select a Database type from
the list as shown in the above
screenshot. Click Connect.
• You have to pass Server
name/ User name and
password to connect. You can
also connect via a direct SQL
query using Advance options.
You can also select
Connectivity mode- Import or
DirectQuery.

22
 Loading Data from various sources – Database – Import
vs Direct Query

• DirectQuery option limits the

option of data manipulation and the
data stays in SQL database.
DirectQuery is live and there is no
need to schedule refresh as in the
Import method.
• Import method allows to perform
data transformation and
manipulation. When you publish the
data to PBI service, limit is 1GB. It
consumes and pushes data into
Power BI Azure backend and data
can be refreshed up to 8 times a day
and a schedule can be set up for
data refresh.

23
Loading Data from various sources – Database – Import
vs Direct Query

Advantages of Using DirectQuery:

Using DirectQuery, you can build data visualizations on large datasets, which is not feasible
to import in Power BI desktop.
DirectQuery doesn’t apply any 1GB data set limit.
With the use of DirectQuery, the report always shows current data.
Limitations of Using DirectQuery
There is a limitation of 1 million row for returning data while using DirectQuery. You can
perform aggregation of more number of rows, however, the result rows should be less than 1
million to return the dataset.
In DirectQuery, all tables should come from a single database.
When a complex query is used in the Query editor, it throws an error. To run a query, you
need to remove the error from the query.
In DirectQuery, you can use Relationship filtering only in one direction.
It doesn’t support special treatment for time-related data in tables.

24
Loading Data from various sources - Azure

• The user can connect

to the database in the
Azure cloud by using
the Azure option.

25
Loading Data from various sources – Online Services

• The user may link

Power BI to various
web services,
including Facebook,
Google Analytics,
Salesforce,
Exchange, and
more.

26
Loading Data from various sources – Others

• The options that are available

in the other category are
displayed

27
Querying Databases and Transformation on Databases
in PowerBI

• After the data gets loaded into the application, you can begin transforming and modeling
your data.
• Power BI Desktop has three views:
 Report view – You can use queries that you create to build compelling visualizations,
arranged as you want them to appear, and with multiple pages, that you can share with
others.
 Data view – See the data in your report in data model format, where you can add
measures, create new columns, and manage relationships.
 Model view – Get a graphical representation of the relationships that are established in
your data model, and manage or modify them as needed.

28
 Querying Databases and Transformation on Databases
in PowerBI

• Access these views by selecting one of the

three icons along the left side of Power BI
Desktop. In the following image, Report
view is selected, indicated by the yellow
band beside the icon.
• Power BI Desktop also comes with Power
Query Editor. Use Power Query Editor to
connect to one or many data sources, shape
and transform the data to meet your needs,
then load that model into Power BI
Desktop.

29
Power Query Editor

• To get to Power
Query Editor,
select
Transform data
from the Home
tab of Power BI
Desktop.

30
 Power Query Editor

Here's how Power Query Editor appears

after a data connection is established:

1. In the ribbon, many buttons are now

active to interact with the data in the query.

2. In the left pane, queries are listed and

available for selection, viewing, and
shaping. Power Query Editor

3. In the center pane, data from the selected

query is displayed and available for
shaping.

4. The Query Settings pane appears, listing

the query's properties and applied steps.

31
The Query Ribbon

• The ribbon in Power Query Editor consists of four tabs: Home, Transform, Add Column,
View, Tools, and Help.
• The Home tab contains the common query tasks.

• To connect to data and begin the query building process, select New Source. A menu
appears, providing the most common data sources.

32
The Transform Tab

• The Transform tab provides access to common data transformation tasks, such as:
 Adding or removing columns

 Changing data types

 Splitting columns

 Other data-driven tasks

33
The Add Colum Tab

• The Add Column tab provides more tasks associated with adding a column, formatting
column data, and adding custom columns. The following image shows the Add Column
tab.

• The View tab on the ribbon is used to toggle whether certain panes or windows are
displayed. It's also used to display the Advanced Editor. The following image shows the
View tab.

34
The Left (Queries) Pane

• The left pane, or Queries pane, displays the number of active queries and the name of the
query. When you select a query from the left pane, its data is displayed in the center
pane, where you can shape and transform the data to meet your needs. The following
image shows the left pane with a query

35
The center (Data) Pane

• In the center pane, or Data pane, data from the selected query is displayed. This pane is
where much of the work of the Query view is accomplished.

36
The right (Query Settings) pane

• The right pane, or Query Settings pane, is

where all steps associated with a query are
displayed.
• For example, in the following image, the
Applied Steps section of the Query Settings
pane reflects the fact that we just changed
the type of the Overall score column.

37
Various Power BI Transformation Operations

• Change the Data type of a Column

• Combine Multiple Tables

• Enter data or Copy and Paste from Clipboard

• Format Dates

• Groups

• Hierarchies

• Joins

• Pivot Table

• Query Groups

• Reorder or Remove Columns

• Rename Column Names

• Rename Table Names

• Split Columns

• UnPivot Table

38
PowerBI Transformation: Change the Data type of a
Column

• When you load a table from any data source, Power BI automatically detects the data
type of a column. However, there may be some situations where Power BI might get
them wrong.
• For example, it may consider amounts, values, or even dates as the text. In these
situations, you can use Power BI change data types of a column option.
• Step 1: Click Edit Queries option. Clicking Edit Queries option opens a new window
called Power BI Power Query Editor.

39
PowerBI Transformation: Change the Data type of a
Column

• Please select the Column for which you want to change the data type. Next, click on the
left corner of the column header (currently it represent ABC text). Clicking in that position
opens a drop-down list of supported data types. Please select the data type that suits
your data. Here, we are selecting the Whole number.

First Approach

40
PowerBI Transformation: Change the Data type of a
Column

Second Approach

Third Approach

41
PowerBI Transformation: Format Dates

• Using PowerBI formatting Date options, you can extract the Year, Month, Day, Day
Number, Day name, Month Start and Month End, etc.
• From the screenshot below, you can see the HireDate column. To demonstrate the date
formatting options, we are going to use this column.

42
PowerBI Transformation: Format Dates

• Please select the Date Column and right-click on it to open the context menu. Select
the Transform option and select the formatting option. For now, we are selecting the Year.
It means this property extracts and displays the Years from the Hire date.

43
PowerBI Transformation: Format Dates

Second Approach

Third Approach

44
PowerBI Transformation: Joins

• Power BI supports all major joins that are available in SQL.

• The following join types are the Standard join types in Power BI and SQL

1. Inner Join: Returns the rows present in both Left and right table only if there is a match.
Otherwise, it returns zero records.

2. Full Outer Join: It returns all the rows present in both the Left and right table.

3. Left Outer Join: It returns all the rows present in the Left table and matching rows from
the right table (if any).

4. Right Outer Join: Returns matching rows from the left table (if any), and all the rows
present in the SQL Right table.

45
PowerBI Transformation: Joins

• To show the join operations on existing tables, please click the Edit
Queries option under the Home tab.

46
PowerBI Transformation: Joins

To
perform
Power BI
Join
operations
on any
table, we
have to
use the
Merge
Queries
button
present in
the Home
tab

47
PowerBI Transformation: Joins

• Merge Queries has two drop downs:

1. Merge Queries: It merges the second table inside this original table.

2. Merge Queries as New: It creates a new table with the result of joining the first and second
tables.

48
PowerBI Transformation: Joins

• The
following
window will
open.
• Use this
window to
select the
Second
Table,
• Join Type,
and
standard
column filed
in both the
tables.

49
PowerBI Transformation: Joins

50
Power Query – Advanced Editor

• The Advanced Editor lets you see the code that Power Query Editor is creating with each
step. It also lets you create your own code in the Power Query M formula language. To
launch the advanced editor, select View from the ribbon, then select Advanced Editor. A
window appears, showing the code generated for the selected query.
• You can directly edit the code in the Advanced Editor window. To close the window, select
the Done or Cancel button

51
Lecture 3

Creating Reports and Dashboards

Create Visuals

• After you have a data model, you can

drag fields onto the report canvas to
create visuals.
• A visual is a graphic representation of the data
in your model.
• There are many different types of visuals to
choose from in Power BI Desktop.
• The following visual shows a simple column
chart.

53
 Create Visuals

• To create or change a visual:

• From the Visualizations pane,
select the Build visual icon.
• If you already have a visual
selected on the report canvas,
the selected visual changes to
the type you selected.
• If no visual is selected on the
canvas, a new visual is created
based on your selection.

54
Create Reports

• More often, you'll want to create a collection of visuals that show various aspects of the
data you've used to create your model in Power BI Desktop.
• A collection of visuals, in one Power BI Desktop file, is called a report.
• A report can have one or more pages, just like an Excel file can have one or more
worksheets. With Power BI Desktop you can create complex and visually rich reports,
using data from multiple sources, all in one report that you can share with others in your
organization.

55
Create Reports (Example)

56
 Create Report

• In Power BI Desktop Report view, you

can build visualizations and reports.
The Report view has six main areas:

1. The ribbon at the top, which

displays common tasks associated with
reports and visualizations.

2. The canvas area in the middle,

where you create and arrange
visualizations.

3. The pages tab area at the bottom,

which lets you select or add report
pages.

57
 Create Report

4. The Filters pane, where you can filter

data visualizations.

5. The Visualizations pane, where you can

add, change, or customize visualizations,
and apply drillthrough.

6. The Format pane, where you design the

report and visualizations.

7. The Fields pane, which shows the

available fields in your queries. You can
drag these fields onto the canvas, the
Filters pane, or the Visualizations pane to
create or modify visualizations.

58
 Visualizations

1. The Fields option in the Visualization pane lets you drag

data fields to Legend and other field wells in the pane.
2. The Format option lets you apply formatting and other
controls to visualizations.
3. The icons show the type of visualization created. You can
change the type of a selected visualization by selecting a
different icon, or create a new visualization by selecting
an icon with no existing visualization selected.

59
Visualizations

• Power BI offers the functionality to visually represent our data or a subset of it so that it
can be used to draw inferences or gain a deeper understanding of the data. These visuals
can be bar graphs, pie charts, etc. Following are some examples of basic visual options
provided in Power BI-
 Card – It is used to represent a single value such as Total Sales, etc.

 Stacked bar/column chart – they combine a line chart( which joins points representing
some values with a line) and a bar/column chart(which represents a value against the
purpose and other optional fields).
 Waterfall chart – It represents a continuously changing value where increase or decrease
in value may be represented by differently colored bars.

60
Visualizations

 Pie chart– it represents the fractional value of each category of a particular field.

 Map-It is used to represent different information on a map.

 KPI-It represents the continuous progress made towards a target.

 Slicer – A slicer has options representing different categories of a field. Selecting that
category shows only the information specific to that category in other visuals.
 Table – A table represents data in tabular form, i.e rows, and columns.

61
 Demo
(PowerBI Sample
Data)
62
Share Reports

• After a report is ready to share with

others, you can publish the report to the
Power BI service, and make it available to
anyone in your organization who has a
Power BI license.
• To publish a Power BI Desktop report:

1. Select Publish from the Home ribbon.

Power BI Desktop connects you to the
Power BI service with your Power BI
account.

63
Share Reports

2. You're prompted to select where in

the Power BI service you'd like to share
the report. For example, your workspace,
a team workspace, or some other
location in the Power BI service. You
must have a Power BI license to share
reports to the Power BI service.

64
Lecture 4

PowerBI DAX and Modeling

PowerBI DAX and Modeling

• DAX stands for Data Analysis Expression Language.

• It is a powerful language, and you must use it to write calculations in Power BI.
• In Desktop, the Modeling tab is powerful because here, you can create a new
Table, Column, Parameter, and Measures.
• You can also manage and view Roles and the relationships between tables.
• To create a Measure or any calculated field, you must use DAX.

66
DAX – Calculated Fields

• Sometimes, we don’t get the columns that we need to perform visualization.

• In these situations, we can create calculated fields that we have shown below.
• Then, we can write simple arithmetic operations or the most powerful DAX functions
within these columns.

1. Calculated Columns

2. Conditional Columns

3. Calculated Measures

4. Calculated Tables

5. Custom Columns

67
Create Calculated Columns in PowerBI

• Power BI Calculated columns help improve data models by adding a new column derived
from the existing columns in a table.
• Calculated columns will be computed and stored in the data model during the data
loading process.
• One of the main advantages of calculated columns is their reusability.
• Once created, we can use this Power BI calculated column in multiple charts.
• If updates or changes happen to the calculated column, it will automatically reflect all its
used places.
• For example, we can find the actual sales by removing the tax from total sales.

68
Create Calculated Columns in PowerBI

• First, you must select the table where you want to create the calculated column. Next,
please click on the New Column option under the Modeling tab.

• Clicking the New Column option opens the Power BI DAX formula bar, as shown below. It
also creates a new column under the selected table with the default name. Use the
Formula bar to write the expression for this new column with the help of DAX operators
and functions.

69
Add a Conditional Columns in PowerBI

• Clicking the Edit Queries option opens a new Power BI Power Query Editor window.
• First, go to Add Column Tab. Under this tab, you can see the Conditional Column button.
You can use this button to add the conditional col to this table.

70
Add a Conditional Columns in PowerBI

• Clicking the Power BI Conditional Column button opens the following

window.

71
DAX – Calculated Fields

• Similarly, we can have

 Calculated Measures

 Calculated Tables

 Custom Columns

72
PowerBI – DAX Business Analytics Functions

 Aggregate Functions

 Date Functions

 Logical Functions

 Math Functions

 String Functions

 Trigonometric Functions

73
PowerBI – DAX Aggregate Functions : SUM

• The DAX Sum function calculates the total or Sum of records in a column and the DAX
Sum Function syntax is

Measure Name = SUM(Expression or Column Name)

To demonstrate these DAX aggregation functions, we have to use Measures. To create a

measure, please click on the New Measure option under the Power BI Home tab, or
Modeling tab.

SalesSum = SUM(EmployeeSales[Sales])

74
PowerBI – DAX Aggregate Functions : Average

• The Power BI DAX Average function is used to calculates the Average of records in a
column. The DAX Average Function syntax is

Measure Name = AVERAGE(Expression or Column Name)

75
PowerBI – DAX Aggregate Functions : MIN / MAX

• The Power BI DAX MIN function finds the Minimum value in a column. The syntax of the
DAX MIN Function is

Measure Name = MIN(Expression or Column Name)

The DAX MAX function finds the Maximum value in a column. The syntax of the DAX MAX
Function is

Measure Name = MAX(Expression or Column Name)

76
PowerBI – DAX Aggregate Functions

• COUNT
• VAR.S - Statistical Variance of complete records
• VAR.P - Statistical Variance of selected or some records.
• STDEV.P - standard deviance of selected records.
• STDEV.S - standard deviance of complete records.

77
DAX Date Functions

• DAX provides various Date Functions such as

 Year,
 Month,
 Day,
 Calendar,
 date,
 time,
 datediff,
 now,
 today,
 utcnow,
 utctoday,
 datevalue,
 timevalue

78
DAX Math Functions

Microsoft Power BI DAX provides various Math or mathematical Functions such as CEILING,
 FLOOR,
 CURRENCY,
 INT,
 SQRT,
 POWER,
 MOD,
 DIVIDE,
 ROUND,
 ROUNDUP,
 ROUNDDOWN,
 LCM,
 GCD, etc.

79
DAX String Functions

• Microsoft Power BI DAX provides various String Functions such as LEN, LEFT, RIGHT,

LOWER, UPPER, MID, SUBSTITUTE, FORMAT, CONCATENATE, CONCATENATEX, REPT,

UNICHAR, VALUES, etc.

80
Thank You