Machine Learning & Big Data Blog

How To Create Reports in Microsoft Power BI

5 minute read
Walker Rowe
image_pdfimage_print

In this article, we’ll show you how to create reports with Microsoft BI.

The approach of Microsoft Power BI is a bit different than other products—it’s not always clear. The basic procedure is to repeat the steps below for each widget (graphical object) that you want on the report.

You can follow along in this article without doing the tutorial yourself. If you do want to work with it, we’ll use the same banking data from .csv files that we used previously, so review that to have some data to work with.

Let’s get started.

(This article is part of our Power BI Guide. Use the right-hand menu to navigate.)

How Power BI works: repeating pattern of steps

You can think of any report as a dashboard. A widget is some kind of visual display: a chart, a table, or just a single metric displayed in a text box.

The basic procedure is to repeat this process for each widget:

  1. Create a data source
  2. Run a transformation (Optional)
  3. Create a query or data model
  4. Pick a visualization
  5. Select fields
  6. Arrange visualization on dashboard

How to create Power BI reports

To illustrate, let’s move through each of these steps. First, we create a data source. That connects to a file, like a .csv, or a database.

Next, you have the option to run a transformation. In our example, we use financial data. We will:

  • Apply a filter to select only negative values (payments)
  • Drop and rename columns
  • Optionally apply a function, such as an aggregation

Step 3 is the natural result of step 2, because you have built up a query in stages.

Alternatively, at this point, you could create a data model. For example, if you have sales and inventory movements in two data sources you can model that. You would create a model to show the common element between tables: product number. (But in the example we’re using, we only have a single data source.)

In step 4, you create a visualization. In this example, we will have a table of transactions. A table is a row and column display. We will also have a single card (like a text box) to show a single number, the maximum transaction amount.

Next, we’ll pick fields from step 4. Finally, in the last step, we’ll position the visualizations on the dashboard.

Now, let’s walk through an actual example.

A hands-on tutorial

Here is the landing page for BI. By default, it shows a pie chart with no data. Notice the three icons on the left:

  • Dashboard
  • Queries
  • Data model

Adding the first visualization

The logical place to start is to select a data source.

The basic procedure is to load and optionally select transform. In most cases, you would want to do a transformation.

For example, let’s click a column, then apply a filter to only have negative values (payments):

Here we select a numeric column, amount. Because it’s a number, we can run a math or aggregation function on it.

We select the Statistics function Maximum:

The result is a scalar (single value), as opposed to a row in a row-column table.

Now click on the new field and give it a meaningful name. Notice that BI keeps track of the steps we have taken.

You’ll also want to rename the query. At this point, BI calls the results of the transformation a query.

Click the close & apply button to close the Power BI editor and return to the dashboard view.

Select the card visualization, then select field maximum from the query maximum.

The card is added to the report:

Adding more visualizations

Now we can add another visualization to show how to build up your report.

We will make a table. Select recent sources and pick the same .csv file. Importantly, we have to go all the way back to the beginning data source because we turned the first source into a query. (We can’t use the query to make a table, since it’s already transformed into a scalar.)

Now we have two queries:

  • wf is a table
  • maximum is the data source or the card visualization

Here’s what the table looks like when attached to the dashboard:

The text looks annoyingly small and graphic-like. It’s not like a spreadsheet, which would be clear and easy to read. (We will show how to clean that up in an upcoming tutorial.)

Finally, move the card over to make room for the table. Select the corner so you can resize it.

That concludes this tutorial. Now, you can begin building your reports with repeating the widget pattern.

Related reading

Turn Data into Decisions using Application Workflow Orchestration

Support business transformation and create competitive advantages
Read Now ›

These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing blogs@bmc.com.

BMC Bring the A-Game

From core to cloud to edge, BMC delivers the software and services that enable nearly 10,000 global customers, including 84% of the Forbes Global 100, to thrive in their ongoing evolution to an Autonomous Digital Enterprise.
Learn more about BMC ›

About the author

Walker Rowe

Walker Rowe is an American freelancer tech writer and programmer living in Cyprus. He writes tutorials on analytics and big data and specializes in documenting SDKs and APIs. He is the founder of the Hypatia Academy Cyprus, an online school to teach secondary school children programming. You can find Walker here and here.