Power BI

Limit of 10 Reports per Dataset/Data Model

Posted on Updated on

NOTE: This information is good as of 7/22/2015 and is subject to change.

*** Update ***

I had posted earlier that this was specific to Free users, but it has nothing to do with Free or Pro. Reposted to reflect the accurate behavior.


I was looking at the Power BI Community Site and someone had indicated that they cannot save a report.

Unable to save report

When looking into that Activity ID, it appears that we have a limit of 10 reports.  This has nothing to do with if you are a Free or Pro user, but rather a resource limitation based on your Dataset/Model.

When you try to save the 11th report against the same Dataset, you get the error above.  If you create an 11th report against a different Model it will work.

Adam W. Saxton | Microsoft Business Intelligence Support – Escalation Services


Refresh for on-premises sources is here!

Posted on Updated on

According to Microsoft PowerBI team:

One of the most awaited and requested feature is now available in Power BI. Starting today, in Power BI you can refresh datasets connecting to your on-premises sources such as SQL Server. Any workbook created using Power BI Designer, Power Query or Power Pivot are supported. For these datasets the Power BI Personal Gateway enables automated refreshes against on-premises data sources in your organization, all via a single gateway you can install directly on your desktop machine.

All common data sources are supported:

  • SQL Server
  • Oracle
  • Teradata
  • IBM DB2
  • PostgreSQL
  • Sybase
  • MySQL
  • SharePoint List (on-premises)
  • File (CSV, XML, Text, Excel, Access)
  • On-premises SQL Server Analysis Services models (uploaded data; not live connections)
  • Folder
  • Custom SQL/native SQL

this is really exciting  – even a normal workstation can do the work!

Dear Accountants: PowerPivot is your friend!

Posted on Updated on

…64bit version is a must have!

by: Mike Griffin

Intro by Avi: Power Pivot and Power BI tools can be used to transform BI for a wide array of industry verticals and vocations. But it is especially suitable to some roles; Accountants are probably at the top of that list! Our friend Mike here is a Financial Manager in the interesting vertical of Cruise Lines. And has a post for us describing just one of the ways they are using the Power BI tools, in this case to find needles in the haystack. Take it away Mike…

Accountants are NOT typically data GEEKS

Accounting related problems open doors to a different set of applications for PowerPivot and PowerQuery. Although it’s fair to say most accountants like numbers, an affinity for numbers does not always translate into a love for data – especially lots of data. This example illustrates how PowerPivot and PowerQuery can be used to help automate accounting related tasks that can be incredibly time consuming when a lot of data is involved.

The scenario I’m presenting is not sophisticated in terms of DAX formulas and is very simple from a data modeling point of view. However, it’s an incredibly useful application of the tools we use as PowerPivot enthusiasts that can save valuable time when closing the accounting period.

The Accounting Need: Remove needles from the hay stack

Use Power Pivot and Power BI to look for the proverbial needles in your data haystack
Use Power Pivot and Power BI to look for the proverbial needles in your data haystack

In this scenario, I need to reverse invoice specific journal entries that were originally posted as part of an automated process between an internal database and our accounting software. This entry is posted as a batch with thousands of other invoices (the original journal entry can’t just be reversed).

A data source is available with details at an invoice level that represent the batch total. However, this data source does not share the same names for the account details required to reverse the original journal entry and it is not data-model friendly.

The Goal:

  • · Extract 17 of the 118 columns available in the detailed data source with the invoice level detail.
  • · Map the 17 columns to the appropriate general ledger accounts in another table and add other details for the journal entry.
  • · Isolate a subset of the invoice level details based on another table with the specific invoices I need.

I’m working with three different tables:

  1. EntryAccounts” table that translates the column labels in my detailed data set into the appropriate journal entry details such as account number, department, etc. (see Step One below)
  2. BookingList” table that helps me extract the specific invoices I’m interested in from the detailed list of thousands. (see Step One below)
  3. RevDetail” table which is a detailed data set with all of the invoice specific information I need (the transformed version of this table is in Step Two below).

Step One:

Create a table that maps the appropriate columns from the data source to the general ledger accounts I want and a list of the specific invoices I want to isolate.

  • · Load each table into PowerPivot
Table One: EntryAccounts Table Two: BookingList
Accounting Detail for PowerPivot Invoice List
The Rev_Description column is a list of the column headers I’m interested in from the detailed data source with invoice specific details. The Invoice List table helps filter the list of thousands of invoices in my large detailed data set to the smaller subset I’m interested in.

Step Two:

I use PowerQuery to turn the detailed data source table with roughly six thousand rows and 118 columns into a three column data-model friendly format. I’ve “un-pivoted” the columns so I can create a relationship between what had been column headers to the unique row values in the Rev_Description column in the “Entry Accounts” table. Note: this step turns the table into over 85K rows in this case. (A tall and skinny table – lots of rows, fewer columns – is far more ideal shape for Power Pivot than short and squat).

This is the PowerQuery view of what becomes the “RevDetail” table in PowerPivot.

Table Three: RevDetail
Unpivot data with PowerQuery Steps to unpivot with PowerQuery
  • · Load the table into PowerQuery, un-pivot the data, & filter the attribute column for the columns I’m interested in. These columns are synonymous with specific account numbers on my “EntryAccounts” table.
  • · Remove unwanted columns and load this table into PowerPivot if using Excel 2010 (the query can be loaded directly into the PowerPivot data model in Excel 2013 – a very useful feature!)

Step Three:

Relate the invoice level data to the subset of invoices & appropriate accounting details.

Create Relationship - revised

  1. The Attribute column in the “RevDetail” table is related to the “Rev_Description” column in my “Entry Accounts” table. The Attribute column had been 17 different columns in the original data source.
  2. The Invoice column in the “RevDetail” table is related to the Booking column of my “Invoice List” table.

Step Four:

Create a pivot table that brings the data source, specific invoices of interest, and mapped accounting details “in line” for review and ready to scan with some VBA.

Flag data with PowerPivot Related

Create calculated columns to create filters for the pivot table output.

PowerPivot Output

I’ve pulled the appropriate details into a pivot table. The relationship between the RevDetail table and the BookingList table help me filter down to the invoices I care about. The relationship between the RevDetails table and the EntryAccounts table allow me to pull in the accounting related detail that is not in my detailed data source. From here, I use a little VBA that scans the pivot table and moves the data into a template that is designed to interface and upload with the specific accounting software being used.

Journal Entry with VBA and PowerPivot

The Result:

This process takes no more than ten minutes to gather and upload all required data, leading to a journal entry that can be uploaded to an accounting system. Creating these entries had taken 15 to 20 minutes per invoice prior to implementing PowerPivot as a tool to automate this process. This example was only isolating four invoices; the list is often longer. The time savings can be huge!