How to build a Dashboard in Power BI Desktop?

Business Intelligence (BI) plays an important role in the strategic planning of organizations. It is used for measuring performance progress toward business goals, performing quantitative analysis, reporting and data sharing, and identifying insights.

What is Power BI?

Power BI is a visualization or business analytics service provided by Microsoft. It is helpful and efficient to provide interactive insightful visualizations and business intelligence capabilities with an interface simple enough for end-users to create their own reports and dashboards.

In this Power BI tutorial, you will learn how to build a dashboard in Power BI desktop.

Why choose Power BI?

• Power BI connects easily to nearly any data source, be it corporate Data Warehouse, Microsoft Excel, or web-based data.
• Power BI allows us to connect to live data and perform various analyses on it.
• Most tools require an extreme amount of work to make maps correctly. Power BI made it pretty seamless.
• Easily drag and drop data elements into the rows and column shelves for efficient analysis.
• The visualization tool from the toolbar menu helps you get possible suggestions for the selected Dimension and Measure.

Build a Dashboard in Power BI Desktop

Before, I tell you in this Power BI tutorial that how to build a dashboard on Power BI desktop. Let see the best way to approach any BI tools using the below technique.

Connect to the data source

To connect the various data source in the Power BI desktop, you need to click on the Get data under the Home tab or section as shown below.

Once, you connected to the data source. Click on the “Transform data” icon to open the Power Query Editor for data transformation which is present in the “Home” section.

The dialog box will appear that would be Power query editor where you transform data and perform data modeling.

Power BI Query Editor

1. Ribbon – The user interface for the editor is quite similar to Excel and uses a visual ribbon-style command center. It organizes data transformation commands and other power query options into 5 main tabs.
2. Query List – This area lists all the queries in the current workbook. You can navigate to any query from this area to begin editing it.
3. Data Preview – This area is where you will see a preview of the data with all the transformation steps currently applied. You can also access a lot of the transformation commands here either from the filter icons in the column headings or with a right-click on the column heading.
4. Formula Bar – This is where you can see and edit the M code of the current transformation step. Each transformation you make on your data is recorded and appears as a step in the applied steps area
5. Properties – This is where you can name your query, when you close and load the power query to an Excel table. It creates a table with the same name as its source query if the table name isn’t already taken. The query name also depends on how the M code will reference this query if we need to put it in another query.
6. Applied Steps – This area is a chronological list of all the transformation steps that have been applied to the data. You can move through the steps here and view the changes in the data preview area. You can also delete, modify or reorder any steps in the query here.

Power Query Editor –HOME Tab

1. Close– You can access the Close & Load and Close & Load To options from here. These are also available in the File tab menu.
2. Query– You can refresh the data preview for the current query or all query connections. You can also open the properties settings and the advanced editor for the current query and there are options under the Manage button to delete, duplicate or reference the current query.
3. Manage Columns – You can navigate to specific columns and choose to keep or remove columns.
4. Reduce Rows– You can manage the rows of data from this section. There are lots of options to either keep certain rows or remove certain rows. Keep or remove the top N rows, the bottom N rows, a particular range of rows, alternating rows, duplicate rows, or rows with errors. One option only available for removing rows is to remove blank rows.
5. Sort- You can sort any column in either ascending or descending order.
6. Transform- This section contains a mix of useful transformation options like Split Columns, Group, Data Type, Use First Row as Headers, Replace Values.
7. Combine- This section contains all the commands for joining your query with other queries. You can append queries or combine files when working with a folder query.
8. Parameters- It allows you to create parameters for the queries. For example, when setting up a from folder query, you may want the folder path to be a parameter so you can easily change the location. You can create and manage existing parameters from this section.
9. Data Sources– This section contains the data source settings including permissions management for any data sources that require passwords to access.
10. New Query– You can create new queries from new data sources or previously used data sources from this section.

Power Query Editor –TRANSFORM Tab

1. Table– This section contains commands that will transform the entire table. You can group and aggregate your query, promote rows to headers, demote headers to rows, transpose your data, reverse row order, and count rows.
2. Any Column– This section contains commands that will work on any column of data regardless of data type. You can change the data type, automatically detect and change the data type, rename the column heading, find and replace values, fill values down (or up) a column to replace any blanks or nulls with the value above it (or below it), pivot or un-pivot columns, move columns to a new location or convert a column to a list.
3. Text Column – This section contains commands for text data. You can split columns with a delimiter, format the case, trim and clean, merge two or more columns together, extract text, and parse XML or JSON objects.
4. Number Column – This section contains commands for numerical data. You can perform various aggregations like sums and averages, perform standard algebra operations or trigonometry, and round numbers up or down.
5. Date & Time Column – This section contains commands for date and time data. You can extract information from your dates, times, and duration data.
6. Structured Column– This section contains commands for working with nested data structures such as when your column contains tables.

Power Query Editor –ADD COLUMN Tab

1. General– This section allows you to add new columns based on formulas or custom functions. You can also add index columns or duplicate a column from here.
2. From Text – Very similar to the From Text section in the Transform tab, but these commands will create a new column with the transformation.
3. Number – Very similar to the From Number section in the Transform tab, but these commands will create a new column with the transformation.
4. Date & Time – Very similar to the From Date & Time section in the Transform tab, but these commands will create a new column with the transformation.

Power Query Editor – VIEW Tab

1. Layout– This section allows you to either show or hides the Query Setting pane (which contains the properties and applied steps) and the Formula Bar.
2. Data Preview – This section allows you to show or hide white-space characters or turn the font into a mono-space font in the data preview area. This is handy when dealing with data delimited by a certain number of characters.
3. Columns- This allows you to go to and select a certain column in the data preview. This command is also available in the Home tab.
4. Parameters- This allows you to enable parameterization in data sources and transformation steps.
5. Advanced– This will open the advanced query editor which shows the M code for the query. This is also available from the Home tab.
6. Dependencies– This will open a diagram view of the query dependencies in the workbook.

How to create a visual?

The best part of the Power BI tutorial and may be confusing as well that which visual to choose to provide the best insights. One has to drop the column fields from the table to populate the detail and values in the chart or visual. It depends on what kind of visual you want to show to the audience. There is no such golden rule but you can follow as per below best practice used:

Difference between Calculated Column and Measure

Calculated Column

• Row by row calculation which means Row Context usually.
• It is stored in the memory and consumes RAM.
• It is calculated at the time of refreshing the report on either a scheduled basis or manual.

Measure

• The measure is calculated based on all filters which means Filter Context usually.
• It is neither stored nor pre-calculated.
• When you put it on a report page and you change a slicer, filter, or click on a column chart or any other visual to highlight then it affects the measure’s value.
• It consumes the CPU for calculation.

Power BI Pricing

Advantages of using Power BI over Tableau

In Power BI desktop you can download other visualizations from the marketplace or you can create your own visualization whereas in Tableau you are restricted to use the visuals which are provided by the Tableau Community.

Power BI service gives you a method to provide Quick Insights of your data. It means you can publish your data to Power BI service and within the dataset you can use the functionality of Quick Insights which creates different graphs automatically based on your data. There is no such functionality in Tableau.

Power BI added advantage is the Total Cost of Ownership, especially if you are in an organization that has a lot of occasional users. Moreover, you are in specific ease of use functions, which will vary from case to case.

You can download the Power BI desktop using the link Here. We hope the Power BI Tutorial help you to build the interactive dashboard.