Creating a Power BI Report

With Content Catalyst's integration of Power BI, users can easily upload data and display the visualisations within their platform, both directly in textual reports or as standalone data dashboards.

This article will outline the basics of creating a Power BI report, from importing data to presenting it in a view and building pivot-style tables and charts.

To get started, you will need to set up a Power BI account.

For more information on initially setting up a Power BI account, see here.

 

A Power BI report is a collection of visualisations, These can be created easily via the Power BI Desktop application.

Power BI Desktop is available from the Microsoft Store and provides all the tools needed to create suitable reports from data.

Importing data into Power BI

The first step of creating a report is to import the relevant data into Power BI.

This can be from a wide range of sources, including:

• Excel workbooks
• Text/CSV Files
• JSON files
• PDFs
• SQL
• SharePoint

Power BI can also connect to several database systems.

For the full range, see the following Microsoft Article: Data sources in Power BI Desktop. 

When first opening the Power BI Desktop application, select a data source to create a new report.

For this example, an Excel spreadsheet will be used.

Select the Excel workbook option and choose the relevant document.

In the Navigator window, you can see that Power BI has separated the Excel file into individual sheets.

Select the relevant sheets you want to import by selecting the checkboxes. You can also preview the data available on the appropriate sheets prior to importing.

To import, select the ‘load’ option. This will import the data as it is, making no changes to the original data.

If you need to amend any data or fields before importing, select the ‘Transform Data’ option.

This allows you to edit the data away from its original source material. A good example of why this would be used is when the user creating the Power BI report doesn’t have edit permissions on the data source Excel sheet. The user will be able to make changes by using this 'Transform data' option.

Import the data using either option.

After a loading splash screen, the data will be imported.

This will now appear on the right-hand side of the application under ‘Data’. See below

Power BI Desktop Views

Power BI desktop has various views that offer different methods of modifying the data for your reports.

To switch between the different views, simply select the view icon on the left-hand menu.

See a brief overview of the key views below.

Report View: 

Main section of Power BI Desktop where a report can be viewed once data and visualisations have been added.

This is where the visualisations will be constructed from the data sources.

New data sources can also be imported from this view via the ‘Get Data’ option.

Table View / Data View: 

In the Table View or Data Veiw each of the imported data sources or sheets can be viewed separately in more detail.

You can also expand each section and select each column individually using the dropdown arrows, see below.

If you need to refine or amend the data, you can do this via the Home tab under queries > Transform data option or right-click and select Edit query.

This will open the Power Query editor window.

Power Query Editor:

The power query editor allows users to modify any of the data fields before importing them into the report.

It is important to note that making changes here does not affect the original data source.

To change a row or column simply right-click on the relevant row and use the dropdown menu to select the relevant option.

Any changes made in the Power Query Editor are stored as queries on the editor's left-hand side under ‘Queries’.

To save, select ‘Close and Apply’. This will close the Power Query editor and return users to the main views.

Model View: 

This view displays the data model.

Power BI will be able to detect the relationship between tables that have been imported.

For example, if one table has a ‘Customer ID’ column, it will match up with another table with the same column.

If the relationship has not been detected, users can drag an item across to match the relationship of the data types.

Building Report with Visualisations 

In Power BI, a report is simply a collection of Visualisations. To create a report from a previously imported data source, first, select the ‘Report View.’

A collection of available visualisations can be seen on the right-hand side under the Visualisations section. See below.

To display the data as a table or chart, users can simply click on the available visualisations and add them to the report canvas.

This is then marked as a placeholder that can be amended or moved around later.

To add the relevant data, expand the data field and drag the preferred column or row over to the visualisation placeholder. See the examples below.

Pivot table example:

Power BI uses the ‘Matrix’ Visualisation to create the equivalent of a Pivot table.

Select Matrix in the Visualisation menu.

Add the relevant Rows, Columns, and Values to the fields.

The results will now be generated in a Pivot-style table below.

Example of charts

There are several visualisations that can be used to create charts.

First, select the relevant chart visualisation you wish to use to add it to the report.

Using the same method as before, drag and drop the relevant data to the X and Y axis dropdown fields to apply this to a chart in the report.

The count variation can also be amended using the X & Y dropdowns.

Various filtering options are available within the ‘Filters on this visual’ menu.

Adding Additional Report Pages  

Once the report starts to take shape and the page canvases start filling up, users can add new pages by selecting the + symbol in the bottom right corner.

Users can also rename each page, similar to how Excel works with sheets.

See below.

That’s how easy it is to create a basic Power BI Report.

 Further Reading 

For more in depth Power BI training see the following recommended sources.

Learn Microsoft Power BI