Introduction to PowerBI Article 1: create the first PowerBI report and powerbi report

Introduction to PowerBI Article 1: create the first PowerBI report and powerbi report

PowerBI is a new-generation interactive report tool of Microsoft. It converts related static data into cool and visualized data, and dynamically filters data based on filter conditions, analyze data from different perspectives and granularities. PowerBI consists of two parts: PowerBI Desktop and PowerBI Service. The former is used by report developers to create data models and report UIS. The latter is used to manage reports and user permissions, and the Web Portal for viewing reports (Dashboard ). Before creating a report for PowerBI, download the PowerBI Desktop development tool and register the Power BI service account. After registering the Service account, developers can Publish (Publish) to the cloud with one click, you only need to open the URL link in the IE or Edge browser to view report data within the permitted range.

I,Understanding the main interface of PowerBI Desktop

Open the PowerBI Desktop development tool. The main interface is very simple. Multiple panels commonly used in development reports are distributed. Each panel plays an important role:

• The top menu is the main menu. Open the Home menu and use "Get Data" to create a Data connection. Use "Edit Queries" to Edit the Data source;
• The left border is Report, Data, and Relationships, which are used to switch views during Report development. In the Relationships interface, Data Relationships are managed. Data Modeling is the key to interactive presentation of Report Data;
• The right side is Visualizations and Fields, which are used to design the report UI. The system has multiple built-in visual components to create complex and beautiful reports;
• The bottom border is the Report Page. The "+" sign is used to create a Page. PowerBI allows you to create multiple pages in a Report. Multiple pages share Data and Relationships;

2. load data

In the Home Main Menu, click "Get Data" to load Data from multiple Data sources (documents, databases, Azure, etc.). In PowerBI Desktop, each data source is abstracted into a "Query". When loading data, PowerBI supports editing the Query, editing the Query in the Query Editor, and cleaning and converting the data, to meet complex business needs.

1. Load the Excel sample data

Sample Data: download this sample Excel workbook, download Excel to the local host, select the Excel Data source type, and click "Connect" to specify the Excel file path:

Select the Sheet to be loaded and click Edit. The Query Editor will open and Edit the data. In this example, click Load to Load the data in Excel to the report, click the "Data" panel on the left to view the loaded Data. For numeric Data, there is a plus sign (Σ) in front ):

2. Edit Query

Each data source is abstracted into a Query. By defining corresponding data conversion operations, you can apply custom data modification operations when the dataset is loaded to PowerBI, you do not need to modify the data source. In the Data view, click "Edit Queries" in the Home menu to Edit and convert the "Query", such as cleaning dirty Data, deleting redundant columns, and adding new columns, the data type of the conversion column. In "Query Settings" on the right, "Applied Steps" explicitly lists the Query editing Steps. After editing, click "Close & Apply" to modify the Query.

In the menu Transform, PowerBI provides a wide range of data conversion functions to meet your complex analysis needs.

3. Add a data column YearMonth.

When the data Column is YearMonth, Combine the Year field with the MonthName field (Combine), click Add Column in the menu, hold down CTRL, and select the Year and MonthName fields at the same time, select "Column From Examples" From the "Add Column" menu ":

Double-click the Column created on the right, and enter the Year and MonthName values of the same data row as a Combine. As an Example, PowerBI uses the result of user input, automatically detects the value of the derived column and generates a formula for calculating the derived column. You can view the formula above the data table:

Transform: Text. Combine ({Text. From ([Year]), "", [Month Name]})

Click OK, modify the column name Combined to YearMonth, switch to the Home menu, click "Close & Apply", Apply Query editing, and Close the Query Editor form.

3. Add visualization Components

Before creating a report, you must be familiar with the relationship between the report data and its data. In this example, there is only one data table, and all the data and their Relationships are stored in one data table. In the Relationships view, there is only one single table.

Click "Report" To Go To The Report Editing page. Use the visual components in Visualizations to design the Report UI.

1. Use Slicer)

Slicer is a Filter, and each CheckBox is an Item. Click Select and click again to cancel the selection. Press CTRL and hold down to select multiple items, indicates that the Filter is not applied to the data. If the Filter is not selected, it is different from the full Filter. According to the internal running principle of PowerBI, if no Slicer option is selected, PowerBI does not perform data filtering. If all options are selected, PowerBI performs data filtering. In the data model, there may be multi-layer relationships between data tables. The unselected and all-selected results may be different. In the subsequent data modeling chapter, I will explain this.

For example, drag a Slicer and use Country as the Filter.

Each Visualization component needs to set the Fields attribute to Drag the data Field Country from the Fields list (Drag) to the Field, powerBI automatically de-duplicates the data (Distinct), displays only unique values, and sorts the data according to the displayed values.

On the right side of Field is a brush image used to change the display attribute of the visualization component. You can try to modify it to customize the UI Display Effect of the data.

Each visualization component has three levels of filters: component level, Page level, and Report level, which are used to filter data. This filter is set statically, data is not dynamically filtered Based on the selected Filter.

2. Sort the Slicer display.

PowerBI supports sorting data values. During sorting, the visualization component performs sorting based on the sorting value (Sort) to Display the data Display value at the corresponding sequence position. Therefore, the sorting operation uses Sort By Column and Display By Column. By default, the displayed Column is the sorting Column; you can modify the default sorting behavior in the "Modeling" menu. When the component displays the data in the Column1 data column, it follows the order of Column2 values in the other data column.

In Fields of the Right Border, select the display Column for sorting. In the "Modeling" menu, select "Sort By Column". The default sorting Column is the display Column, you can select other data columns as sorting columns.

3. Use the Line Chart visualization component

Select the Line chart component from the Visualizations list and select the Product field from the Axis attribute. The visualization component displays data based on the Product. Each Product is a dimension and a view of data analysis; select the Gross Sales and Sales fields for the Values attribute. The visualization component displays two curves. The curve Values are Gross Sales and Sales divided by Product, which means that for each Product, the values of Gross Sales and Sales are calculated respectively.

4. Use the Stacked column Chart visualization component

Group explicit report data, set Axis to YearMonth, Value to Profit, Legend to Product, and Legend to group again, in this example, the Legend attribute is set to Product, which means that when the Axis attribute is a Month (Year Month), PowerBI groups values by Product, set the Profit of each Product separately (the Value attribute is Profit );

The data presentation of the visualization component is not perfect, because the YearMonth at the bottom is not sorted by the natural month, but by the character order, in order to modify this "flaw ", the default sorting behavior of the component must be changed so that it can be sorted by the value of the sorting column. Because the data table contains the Date field, it can be sorted by the Date field, and the displayed field is YearMonth. The implementation steps are very simple. There are two steps:

Step 1: select the YearMonth field from the Fields list on the right,

Step 2: Open the menu "Modeling" and click "Sort by Column". The default sorting field is YearMonth. Select the Date field as the sorting Column.

After the YearMonth sorting column is modified, the component is displayed normally. The YearMonth axis increments from left to right based on the natural month.

4. First PowerBI report

When you click the option in the Slicer visualization component (Year, Country), the data in the right and the following visualization components automatically change. This interactive "linkage" Change is through the Relationship) in this example, because the report has only one data source, the relationship is hidden in a single table. For interactive relations between multiple tables, you can go to the "Relationships" panel, through data modeling, I will detail the Data Modeling and relationship of PowerBI and the Implementation of Dynamic Association in Chapter 2: Data Modeling of PowerBI.

 

Reference:

Getting started with Power BI Desktop

Add a column from an example in Power BI Desktop

Power BI documentation

Power BI Guided Learning

Microsoft's powerful BI