Blog
Overview of Power BI and its Components - Meritshot
February 2, 2024
Understand the key components of Power BI. Learn how its tools support data visualization, reporting, and advanced business analytics.
Introducing Power BI………………………………………………………………………. 3
Parts of Power BI………………………………………………………………………… 3
The flow of work in Power BI………………………………………………………. 4
Power BI Desktop:………………………………………………………………………….. 4
Power BI Desktop Interface: The Report has five main areas……………. 5
Querying Data from CSV…………………………………………………………………. 6
Query Editor……………………………………………………………………………….. 6
Exercise 1: Get Started with Query Editor………………………………………. 6
Exercise 2: Connecting the data from the Excel Source……………………. 7
Interface of Query Editor……………………………………………………………… 9
Exercise 3: Clean, Transform the data (Removing Nulls)……………….. 11
Calculated Measures Using DAX……………………………………………………. 13
Exercise 4: Creating of the Measures using DAX………………………….. 13
Exercise 5: Change the format of the Measures……………………………… 14
Creating Reports & Visualizations…………………………………………………… 15
Exercise 6: Creating your first Visualization (Completion % of All Goals) Gauge Chart……. 15
Exercise 7: Importing a Theme to a Power BI Desktop File……………. 16
Exercise 8: Changing the Color of the Gauge………………………………… 17
Exercise 9: Changing the Title of the Gauge Chart………………………… 18
Exercise 9: Creating the Stacked Column Chart…………………………….. 19
Exercise 10: Sorting the Goals in the right order……………………………. 20
Exercise 10: Filters in Power BI 21
Power BI is a suite of business analytics tools which connects to different data sources to analyze data and share insights throughout your organization.
There are 3 Parts of Power BI.
Power BI Desktop: It is a Windows desktop application (Report Authoring Tool) which Lets you build queries, models and reports that visualize data.
Power BI Service: Power BI Service is cloud based Software as Service Application which allows us to create dashboards, Setup schedule data refreshes, Share the reports securely in the organization.
Power BI Mobile: It is an application (App) on mobile devices which allows you to interact with the reports and dashboard from Power BI Service.
A common flow of work in Power BI begins in Power BI Desktop, where a report is created. That report is then published to the Power BI service, and then shared so users of Power BI Mobile apps can consume the information.
It doesn’t always happen that way, and that’s okay, but we’ll use that flow to help you learn the various parts of Power BI, and how they complement one another.
Power BI Desktop is report authoring tool that allows you to create reports, queries, Extract Transform and Load the data from data sources and model the queries.
Power BI Desktop Interface: The Report has five main areas:
You can import and clean data from Oracle while working in Power BI.
Query Editor, allows you to connect to one or many data sources, shape and transform the data to meet your business needs, then load the queries into the model into Power BI Desktop
This below step provides an overview of the work with data as well as connecting to data sources, shaping the data in Query Editor
Note: With no data connections, Query Editor appears as a blank pane, ready for data.
Navigate to the Strategic Plan and Dashboard Folder and Choose
Click on Open ( ) at the bottom of the screen
Click OK ( )at the bottom of the screen
Query Editor consists of 4 Parts
The Ribbon in Query Editor consists of four tabs
Home Tab: The Home tab contains the common query tasks, including the first step in any query, which is Get Data.
Transform: The Transform tab provides access to common data transformation tasks, such as adding or removing columns, changing data types, splitting columns, and other data-driven tasks.
Add Column: The Add Column tab provides additional tasks associated with adding a column, formatting column data, and adding
custom columns. The following image shows the Add Column tab.
View 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.
The Left pane:
The left pane displays the number of active queries, as well as 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 center (data) pane:
In the Center pane, or Data pane, data from the selected query is displayed. This is where much of the work of the Query view is accomplished.
The Query settings pane:
The Query Settings pane is where all steps associated with a query are displayed.
Exercise 3: Clean, Transform the data (Removing Nulls)
Removing the unwanted rows in the query.
8. Home Tab > Reduce Rows section > Remove Rows > Remove Blank Rows
Notice that null records are eliminated, and new steps is added for the transformation you applied to the query in the query settings pane of the selected query.
Note: Each step, you do in the Query Editor is recorded in Applied Steps of Query Settings pane.
9. From Home Ribbon > Click on Close & Apply
Note: After Close & Apply the query is added to the model for report development.
In general, Measures are used to calculate aggregates, such as the sum or average of a column. Measures are calculated at the time of your query, which means that they aren’t stored in your database, but use processing power to execute a query at the time of your request.
Exercise 4: Creating of the Measures using DAX
10. Be on the Report view, From the Fields Pane, click on the Ellipses (More options) of the Input Query, Click on New Measure.
11. In the Expression Bar, Type in
Overall Completion% = sum (Input [Completion%]) /(COUNTROWS(Input)*100)
12. Click on Commit to validate the Expression
Note: After you commit, if there are any errors in the expression, the expression will be highlighted with red curly line.
Exercise 5: Change the format of the Measures
13. Expand Input query under Fields pane, Select Overall Completion %, and from the Modeling ribbon, Click on the Format under the formatting section and select Percentage.
14. Make the decimal points to Zero of the Overall Completion % measure under formatting
Exercise 6: Creating your first visualization (Completion % of All Goals) Gauge Chart
15. Click on Visualizations Pane and Click on Gauge Chart
Note: Make sure the Visualization is selected before dropping the fields.
16. Expand Input Query, Drag Overall Completion% to the Value section of the Fields pane of the gauge Visual
Exercise 7: Importing a Theme to a Power BI Desktop File.
With Report Themes you can apply design changes to your entire report, such as using corporate colors, changing icon sets, or applying new default visual formatting. When you apply a Report Theme, all visuals in your report use the colors and formatting from your selected theme.
17. From the Home Ribbon of the Report view, click on the drop down of the Switch Theme under Themes section and select Import from the Drag Overall Completion% to the Value section of the Fields pane of the gauge Visual
A window appears that lets you browse to the location of the JSON theme file
18. Navigate to the Strategic Plan and Dashboard folder o the Desktop and select Power BI Color Json file
19. Click on Open ( ) at the bottom of the screen
You will get a success message once the theme is imported successfully.
Exercise 8: Changing the Color of the Gauge.
20. Select the Gauge Chart and Click on the Format of the Gauge Chart, Expand Data Colors properties, click on the drop down of Fill property and select light blue color
After the changing the color the gauge chart looks like the one below.
21. Click on the drop down of Target property and select Black
Exercise 9: Changing the Title of the Gauge Chart.
22. Expand the title property of the Gauge chart, Change the title text to “Completion% of All 4 Goals”.
We are done with our first visualization. We will create few more visualizations.
Exercise 9: Creating the Stacked Column Chart.
23. Click anywhere on the Canvas other than the visuals, select Stacked Column Chart and bring the visual next to the Donut Chart.
24. Expand Input, Drag Overall Completion% to the Value section, Goal Detail to the Legend, Goal to the Axis of the Fields pane of the Stacked Column Visual.
Notice that the goals are not in the right order.
Notice that the Y axis is not 100%
Filters allows the Power BI visual to narrow down or filter to the desired result. We are filtering the visual to show just the data for Goal.
Adjust the height and width of the visual.
Note: It is like MS word Copy (Ctrl + C) and Paste (Ctrl + V)
Notice that the Stacked Column Chart visual is automatically changed to the reflect the data to the Goal 2.