This article is meant to be some kind of a tutorial. After going through it, it should be clearer to you how to create Power Bi report like this on your own. Following this, you can create basically any report you want – company operation report (timesheets, wages, investments), financial analytics (balance sheet, income statement, financial analysis), KPI (budget vs. actual) etc.

Are you interested in making similar report to the one below? It is not hard at all. You can display the report in fullscreen mode by clicking on the icon in the lower right corner.

The report sample is currently only available in Czech (will be revised soon)

Another interesting report which I prepared not so long ago is focused on financial reporting. You can check it out here (Balance sheet and Income Statement)

Data Source – ARAD Database

ARAD database has free access and is maintained by the Czech National Bank. It consists of statistics from all economy areas – fiscal policy, monetary policy, economy figures regarding company classification by its focus and also banking information (deposits, loans etc.)

Dataset for the report is here

After opening Power BI desktop we click on Get Data

Powerbi dashboard dataset

In the next step we insert URL of the source of data for ARAD database. Attention – we make a small edit in the URL (marked red). Marked number declares date till which all the data must be imported. Since we definitely do not want to edit URL after every data update on the source, we can edit the date directly. I use 205012, hence I do not need to change the URL.


Powerbi dashboard dataset uprava

Afterwards we only choose the table and import the data.

Edit Data For Report – The Most Important Part

Data import into the Query editor in a form seen in the source. That means month/year is in rows and name of the marker is in columns. On top of that, empty rows will upload thanks to URL edit adjustment.

Since we forced data until the year 2050 = everything since January 2017 will be empty. We will edit the data because structure like this is not suitable for report. Data preparation is usually the most important part. Carefully processed data base offers high-quality data for the reports without any duplicities or inconsistency. This is how it looks after import:

Powerbi dashboard uprava dat

Step 1 – Filtering Non-Empty Rows

The easiest way to filter non-empty rows is by running the data through a filter. I did that with first column “Domácnosti – Jednodenní vklady”

Powerbi dashboard dataset uprava prazdnych radku

Step 2 – Cleanup, Deleting Unimportant Data

Next thing we need to do is to edit the data in a way that the table consists only of needed information. I was interested only in data on newly provided loans. Therefore I deleted most of the columns (simply choose the column and select delete) and I left only those I am interested in. I also renamed the columns as I needed. You can rename a column by clicking on the name of the column and rewriting it.

Powerbi dashboard dataset uprava sloupcu

Step 3 – Unpivot Columns In Power BI Dataset

Data like this could already be used. But I did not make the final edit and that is so-called unpivot columns. It means transformation of columns into rows. Query editor provides this possibility and you can do it simply by tagging the columns – right mouse button – unpivot

Power bi dashboard dataset uprava unpivot

Result looks like this:

Power bi dashboard dataset uprava hotovy dataset

And it is done, data are ready for reporting just by these easy steps 🙂

Power BI Report Creation

Report creation is fun given well-prepared data. You just need to play with it a little. It is important to realize what do you want to report. I wanted to give users a possibility to filter via loan type and time context. That is why I placed two filters at the top (in red). Additionally, I added information about last known interest rate into the report. Power BI report is dominated by chart reacting to both of the filters. Power is in simplicity.

Powerbi dashboard ukázka

Final power bi report can be simply deployed to our Power BI Account into cloud using icon publish on the upper ribbon. Then you can generate iframe with report which can be added to your web page, reporting portal or you can use and share the report using tool or Email.

Rate this post

Ing. Jan Zedníček - Data & Finance

My name is Jan Zedníček and I work as a freelancer. I have been working as a freelancer for many companies for more than 10 years.
I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums.
I am trying to summarize all my knowledge on this website not to forget them and to put them forward to anyone.

Leave a Reply

Your email address will not be published. Required fields are marked *