Power BI Report Automation

Problem

More and more things are being automated for faster delivery, which is also valid with Power BI. In this article, we look at how to automate the creation of a new Power BI report based on the dataset.

Solution

When working with Power BI daily, sometimes report developers to miss the opportunity to utilize a newly available function that can help out when a “quick” report needs to be developed. In the past, you would have to install and fire up Power BI Desktop and go through a significant design process. The process would include establishing data sources and transformation models and determining the best visuals placed on the design grid. Once the design was finished, that Power BI file would need to be published to the Power BI Service on the web. Such a process would be a fair number of steps to achieve a quick report. However, with a recent upgrade to Power BI, this whole process can be curtailed to a simplified process to establish a quick dashboard with visuals related to a dataset.

Usually, in this section of the tip, a description of installing Power BI Desktop would be provided, but installs are not needed for this particular tip. However, it would be recommended that installing Power BI Desktop would be advantageous for any dashboard developer. You can download the latest version of Power BI desktop to follow along with the examples.

We will actually utilize the Power BI Service to complete the tasks described in this tip for this tip. Thus, you will need access to the Power BI Service through either a work or school account.

From the main Power BI home page, two options are available for creating a new report online, via the Power BI Service. Either the “+” plus sign Create button on the left menu can be selected or the “+ New report” button can be selected in the upper right menu.

Both get you to the same place, which is the Add data to get started (Preview) screen shown below.

Currently only a few options are available to get your data into the report:

You can copy and paste your data set into the entry screen
You can manually enter the data into the entry screen
You can select a currently published dataset

However, only the manual entry or cut and paste method allow for the dashboard report’s auto-generation. Using the “select currently published dataset” will generate a blank report.

Thus, using the entered data manually option opens a data entry grid that allows copying and pasting or simply manually entering the data. Notice the three options available on the design grid. The top dropdown box allows for defining the first row as the header for the table.

For the rows and columns, click on the + buttons as either rows or columns, respectively. Furthermore, the column name can be changed by double-clicking on the column name.

Likewise, the column data type can be adjusted by clicking on the ABC123 button on the left side of each column header. This change can be very helpful as it keeps the data consistent while also preventing certain typing and conversion of data types errors.

In any case, navigating the grid is similar to Microsoft Excel. For example, using the enter key moves the current cell down one cell. Similarly, the data entry can move side to side using the arrow keys to move left and right.

One final note about the design grid, be sure to name your table something appropriate and contextually sound and logical; this is the name of the dataset that users see.

Copy and paste works equally well, but you will need to be careful copying in large amounts of data. The sample financials data is used for the dataset copy and can be downloaded from Download the Financial Sample Excel workbook for Power BI – Power BI | Microsoft Docs.

Be sure not to have a cell selected; if you do, the copy/paste will put all the data in a single cell.

A second issue that you can run into pertains to the data types. Power BI allows the dashboard designer to change the data type to something that does not match the actual data in the column(s).

As shown below, the product field allowed the selection of a whole number for the Product column, which contains alpha characters.

Only upon clicking the Auto-create report button does an error appear. One impressive feature of the error message is that it provides row numbers where the errors could be occurring. That can be helpful if the number of problem rows is minimal (one or two for instance) in an extensive data set.

Three options are available once the data set has been copied in or manually entered to create the report. We are focusing on the Auto-create option. The create a blank report option is similar to the design experience in Power BI desktop, while the generate a data set only option will add the dataset to available datasets on the service.

Clicking on the Auto-create report creates and opens the newly designed with a set of suggested visuals. In the screen print below, you can see that Power BI created 8 visuals, six-column charts, and 2 cards in the upper right corner. Notice in the bottom right corner that the Preview status is highlighted. Power BI makes certain assumptions about the data to provide the initial list of charts.

The robust feature here is that the newly designed report allows for other charts and will automatically make the changes. For instance, if Discount Band is selected from the field list, a new set of charts is added on the right side with the sum of units sold and sum of COGS all by Discount Band. In this case, Power BI knows that Discount Band would be a category field.

Similarly, unchecking a field will remove the related charts to that field. In the illustration below, the country was removed.

In the above, examples, categories were added or removed. New measures can also be selected which would add a whole new row of charts. Thus, using the below example, the Gross Sales measure is selected, and a new set of 3 charts based on Gross Sales is added, plus a new card at the top of the dashboard is added.

However, if you attempt to add a 4^th measure, the error shown in the following screen print results. Power BI currently only allows 3 measures (rows) and 4 categories (columns). Trying to add fields beyond those numbers will result in an error.

Additionally, you will notice on these screens you have limited editing capabilities including not being able to make formatting change or changes to the field lists on a particular visual. Filters and sorting can be applied to the visuals, though.

When you are satisfied with the report, clicking on the save button save the report and the report will automatically be saved to your workspace area on the Power BI Service.

Even with the report saved, the interchanging of categories and measures still can be made by dashboard designers AND dashboard viewers and these changes can be saved by report designers. This dashboard is considered to be using the summarize pane.

Also, at this point, the report can be edited similar to any other report. However, you will receive the below warning. The warning is basically telling you that once you switch to edit mode, the Auto-created Summarize pane options will no longer be in effect and each visual must be modified on its own (like you would do in other reports).

In this tip, we reviewed the process of allowing Power BI to Auto-Create a report for you based on data input or copy into a new dataset. Currently, this method is the only supported option for entering data, but other options are expected in the future for this preview feature.

Author: Scott Murray