In this blog I present how to report accounting data in Power Pivot and Power BI.

Accounts 500 and 501 are revenue accounts and they get increased in credit side. However, some mistakes were corrected with debit postings.

When reporting the revenue, the debits should be deducted from credit.

The total revenue is 380.

However, if you want to report dynamically by month or by account, it’s better to call up Power Pivot and Power BI.

Get the data to the Power Query.

Transform data.

As the date is in Finnish format, take options and file – settings – query options.

Change regional settings to Finnish.

Change data type for date column as date. Data type for account was changed to text as we have no plans to execute any calculations based on account number. Debit and credit should be number type because they are used in measures.

File – Close & Load to…

Only create connection and add this data to the data model.

A measure to sum up the debit postings.

Another measure for the credits.

The total measure to count the net postings, meaning credits minus debits.

Select totals per account.

The results in Excel.

Let’s do the same in Power BI, in case you prefer using Power BI.

Get data – CSV.

Transform data.

Change data type for account as text. No plans to use account as numeric data.

Add calculated column Date – month – month.

Close & Apply.

Then we create measure just like in Power Pivot.

Sum for debit side.

Sum for credit side.

Totals are counted credit minus debit as revenue increases in credit side.

The results are the same as in Excel. Power BI is presenting the results more graphically than Excel.

In this blog page I show how an Access database can be used as source of data for Excel Power Pivot or Power BI.

The Access database table consists of three fields: indexed ID field, product and sales volume.

The table is shown here is design view. ID is the field with primary key.

For the product-field a validation rule is set that only validated values for the fields are P1, P2 and P3.

In Excel select data – get data -from database – from Microsoft Access Database.

Access database is a ready made option as a source type for Excel.

Transform data.

Data type for ID field was changed to text as we have no plans to execute any calculations with ID field.

Take close & load – close & load to.

Create a measure to count total sales volume.

Create a pivot on top of data model.

Let’s create a graph based on data.

The access table was updated with few extra lines, the table was saved.

In Excel side, take power pivot – analyse – refresh.

Sales for P1 increased.

In Power BI select get data – access database.

Access database is, like in Excel, a standard data source type for Power BI.

Transform data. Just like in Excel.

ID data type was changed to text.

Select close & apply – close & apply.

Tot_sales = sum(Sales_t[Sales_vol])

Create a visualization.

Choose dimensions.

The results.

Now let’s change the Access data table.

The sales have been boosted for the product P2.

Fresh the data in Power BI.

The sales report by controller has been updated.

The point in this blog is that Access database is a very suitable source for both Power Pivot and Power BI. A benefit with Access is that data and report are separated. Data is not just a csv file, which was loaded from a system. If csv should be updated, then a new csv file should be taken from the source system.

Once the connection between the Access and reporting has been established, Access can be handled and updated via Access interface and at the same time reporting can be taken elsewhere.