Creating Insightful Reports and Dashboards in Power BI: Best Practices

Power BI is essentially a business analytics tool. It is created to give you an easy way to manage and control the data about your projects, portfolios, and the enterprise as a whole. Data visualization is the new “it” in today’s data modeling and business analytics. Power BI is equipped with the most comprehensive toolbox to visualize data, share it across your enterprise, or even embed it into your app or website. The connectivity of data is also very important. That’s why Power BI allows to intertwine data from hundreds of sources and further mold it into incites and reports.

To get the most out of your data while creating reports with Power BI allows executives and PMOs a chance to make informed decisions faster and easier, connect and explore your organization’s real capacity and potential. Create visual reports to publish and share, sometimes you need a little help. Here’s a cheat sheet we’ve put together with some tips & tricks you can use when creating reports in the Microsoft Power BI Desktop, Power BI service.


Shortlist of Microsoft Power BI features

Microsoft is investing a lot in the development of this product, and therefore there are often updates that expand its capabilities. You can use these versions of the program:

  • Power BI Desktop is designed to develop data models and reports;
  • Power BI Service is an online analogue that specializes in monitoring and analyzing ready-made reports, so the possibilities of designing and working with data there are very limited.
  • As a rule, the desktop version is used as a constructor, after which the developed file is published to the Power BI Service.


Where can I download data from?

Power BI has many built-in connectors for various services and databases, with the help of which you can download the necessary data sets from various sources into a program in a matter of minutes, link them together and build consolidated reports and charts. At the time of this writing, all available connectors are divided into four groups:

1. “File” group:

  • Excel
  • CSV
  • XML
  • Text
  • JSON
  • Folder


2.”Database” group:

  • SQL Server
  • Access
  • SQL Server Analysis Service
  • Oracle
  • IBM DB2
  • MySQL
  • PostgreSQL
  • Sybase
  • Teradata


3.“Azure” group:

  • Microsoft Azure SQL Database
  • Microsoft Azure Marketplace
  • Microsoft Azure HDInsight
  • Blob storage
  • Microsoft Azure Table Storage
  • Azure HD Insight Spark
  • Microsoft Azure Document DB
  • Microsoft Azure Data Lake Storage


4.The “Other” group:

  • The Internet
  • SharePoint list
  • OData channel
  • Hadoop file
  • Active Directory
  • Microsoft ExChange
  • Dynamics CRM online
  • Facebook
  • Google Analytics
  • Salesforce objects
  • Salesforce reports
  • ODBC
  • R-script
  • App Figures
  • Github
  • MailChimp
  • Marketo
  • QuickBooks Online
  • Smartsheets
  • SQL Sentry
  • Stripe
  • SweetIQ
  • Twilio
  • Zendesk
  • Spark

As you can see, you can import data from the best-known databases and services into Power BI using various file formats. After downloading information from available sources in Power BI, you have great opportunities for cleaning and transforming data, since the platform has a powerful ETL functionality.


What kind of visualization can be built?

Having formed a set of data downloaded from various sources, you will see a lot of built-in visualization elements:

  • stacked bar chart
  • stacked histogram
  • grouped bar chart
  • grouped histogram
  • normalized bar chart
  • normalized histogram
  • schedule
  • area chart
  • stacked area chart
  • linear histogram and stacked histogram
  • linear histogram and grouped histogram
  • waterfall chart
  • scatter plot
  • pie chart
  • tree diagram
  • map
  • table
  • matrix
  • completed card
  • funnel
  • sensor
  • multi-line card
  • card
  • key performance indicator
  • slice
  • ring chart
  • visual element r-script

All elements have a wide range of settings aimed at changing the desired parameters: color, background, name, borders, and so on. If you don’t have the standard set, you can upload custom visuals.


Reporting with Power BI Desktop

Power BI Desktop give you additional features for creating reports that provide dynamic information about your projects and portfolios. With Power BI Desktop, you can create complex queries, merge data from multiple sources, create relationships between tables, etc. Power BI Desktop contains a report view where you can create any number of report pages with visualizations. You can move visualizations, copy, paste, merge elements, and so on. Then you can save the Power BI Desktop file anywhere—both on a local disk and in the Cloud.

When you first load data into Power BI Desktop, you will see a report view with a blank canvas. You can switch between reports, data, and links by clicking the icons in the left navigation bar. After adding data, you can add fields to the new visualization on the canvas. To change the type of visualization, select a new type in the “Visualization” group on the ribbon or right-click a visualization and select a type in the “Change visualization type” list.

Note: Experiment with different types of visualizations. The visualization should clearly provide information about the data.

In the beginning, the report contains at least one blank page. Pages are displayed in the navigator to the left of the canvas. You can add different types of visualizations to the page, but the “trick” here is not to overdo it. Too many visualizations on one page will complicate the work and make it difficult to find the necessary information. You can add new pages to the report. Just click the “Create page” button on the ribbon. To delete a page, click the “X” button on the tab of this page at the bottom of the report view.

Note: Reports and visualizations cannot be attached to a dashboard from Power BI Desktop. To do this, you need to publish from Power BI Desktop onto the Power BI online.


#1 How to hide report pages?

When creating a report, you can hide some of its pages, if necessary. This is useful when you need to create baseline data or visual elements in a report, but it is not desirable that other users see these pages. For example, when creating a table or auxiliary visual elements that are used on other pages of the report. There are many other individual reasons why you may need to create a report page and then hide it in a report before publication. Hiding a report page is very simple – just right-click the tab for the report page and select “Hide” in the menu. When hiding a report page, be aware that with Power BI Desktop you still see a hidden report view, even if the page title is inactive.

  • A hidden report page does not appear when viewed in Power BI.
  • Hiding a report page is not a security measure. Users can still access the page and its content using granularity and other methods.
  • If you hide the page and go to the view mode, the navigation arrows will not be displayed.


#2 Learning to use the Query editor

You may recognize the Query Editor in Power BI Desktop. It is like the Power Query add-in capability in Excel 2013. With Query Editor, you can navigate, define, and perform data transformation operations over a data source. The query editor includes these features:

  • Query Editor ribbon.
  • Navigator pane that enables you to browse structured data sources to find the data table that you want to query.
  • Context menus are specific to an element within the editor preview grid—such as a table column.
  • Preview grid that displays a preview of data from the results of each query step. You interact within the Preview pane to shape data and rearrange tables into a subject table to match your data analysis requirements.
  • The Query Settings panel, which includes each query step. A step corresponds to each of the data acquisition or data transformation tasks that can be applied to a query. For more information about how to edit query steps, see Edit query step properties.


#3 Check the data types in Query Editor

When using formulas in Query Editor in Power BI Desktop to load data, sometimes data type settings on columns are not preserved. You should check the data type of columns is correct after doing the following operations:

  • Load data initially to the query tab,
  • First Row as Header,
  • Add column,
  • Group by,
  • Merge,
  • Append,
  • and before pressing loading the data for the first time.

Note: Italics in the data grid do not mean the data type is correctly set; it just means the data is not considered as text.


# 4 Reference queries in the Query Editor

A “Reference” option is also available in Query Editor’s navigator in Power BI Desktop—you just have to right-click one of the queries.

It is useful for the following reason:

  1. The path to the file is stored in the query when you use files as the data source for a query. You’ll save time when you update the paths when sharing or moving a file (or Excel workbook).
  2. All queries load to either an Excel worksheet or the data model (or both). Some queries are intermediary steps and are not intended for end-users. When referencing queries, you can control query loading behavior by right-clicking the query in the navigator and switching over to the “Enable Load” option. If “Enable Load” does not have a check mark next to it, the query is still available in the query tab, and you can use it with other queries. In combination with Merge, Append, and Reference is especially useful. However, since the query results are not actually loaded to the data model, the query will not clutter your report field or your data model.


#5 Reference lines in your report

To define a reference line, you can use a calculated column. Identify the table and column you want to create a reference line to, select “New Column” in the ribbon, and, in the formula bar, type “Target Value = 100.” This will return the value 100 regardless of where it is used. This new column will show up in the Field List. If you need to override the default order (alphabetical), you can tell Power BI Desktop to sort by a different column.


#6 Histograms in the Query stage

Simple Histograms start with determining the query that has the field you want to build a histogram on. Then with the Reference option, a new query is created. You name it “Field Name Histogram.” You use the “Group by” option in the Transform ribbon and select the count rows aggregate. You must ensure the data type is a number. And voilà! You can visualize data on the reports page. This approach is fast and easy to build.

Note: It’s useful to think about the sort order before building any visuals.