Navigating Power BI for More Effective Reporting

Power BI is a business analytics solution that helps you visualize data from multiple sources to provide insights to your organization to facilitate decision making. Power BI comes in different shapes and sizes, from the free Power BI Desktop version that allows you to build reports from most populate data sources and share them locally, to Power BI Pro which includes features to share reports and dashboards online, and Power BI Premium which give you the capability to host your own report server on premise or in the cloud. Power BI Desktop can be used to build sharable reports for all these versions. 

Microsoft has been releasing monthly updates to Power BI Desktop since February 2015 and each release includes cool new features. Let’s take a walk around the latest version of Power BI Desktop and kick the tires a bit. 

The Desktop version of Power BI is a free download and free to use, forever! Microsoft provides free online training through videos and sample files that can get you up to speed in a hurry. And there is a plethora of other sights that provide information, training, answers to common questions and advice on building your reports and dashboards. Power BI Desktop can be used to create reports, lists, graphs, charts, and matrices from over 114 data sources (as of November 2019). Popular data sources include Microsoft Dynamics 365 Business Central/NAV, SQL Server, Access Database, Excel, Text/CSV files, OData feeds, Oracle, QuickBooks online, SAP, and web pages. Visit https://docs.microsoft.com/en-us/power-bi/power-bi-data-sources or create a report in Power BI Desktop using this URL to see the full list. Besides supporting this huge number of data sources, the intuitive tools provided within the application help lower the level of expertise required for creating reports and dashboards.  

The interface is very ‘Microsoft Office’…ish and several the tools mimic those found in Excel. So, creating a graph or chart only requires you to select the icon for the type of graph/chart and then drag and drop the data to use for each axis. Slicers can be added to provide an interface for filtering your graph/chart to view your data from different angles. There are lots of tools for formatting your report or dashboard too including themes, colours, fonts, boarders, and number formatting and even Question & Answer setup. You read that right, Power BI desktop has built in AI that gives you the ability to type questions into your report and get answers from your data. As an example, in your sales report that includes data for periods, regions and salespersons, you can ask Power BI ‘which salesperson has the highest sales’ or ‘which region had the lowest sales’ and your report will be filtered to show you the answer. How awesome is that? 

Under the hood you’ll find a very powerful VertiPaq engine that uses AI to compress your data and improve performance. I once created a report using 4 data sources with a combined total of over 8,000,000 records and it took less than 3 minutes to refresh (download all the data from the sources). That’s one powerful engine, and its carbon footprint is zero ? 

Power BI Desktop has some advanced features as well such as Data Analysis Expressions (DAX) and Power Query M (M) formula languages. Both can be used to perform various calculations on your existing data to create custom fields/columns to answer questions you are otherwise not able to with your data. Both are Autocomplete languages making it easier to adhere to the required syntax. 

In my experience the most important prerequisite to building Power BI reports is knowing your data. If you want to build a report to show your manufacturing capacity utilization you need to know which tables in your ERP system to pull the data from, which fields in those tables hold the information you want to show on your report and which fields can be used to link the tables together. If you know your data, building reports and dashboards is quick, easy and fun. 

Power BI Desktop’s strength is the speed in which you can create reports and dashboards. This also makes it a useful tool when you need to isolate anomalies in your data to identify users that could use some additional training or finding the cause of error messages in your ERP system.  

If you haven’t downloaded Power BI Desktop yet I encourage you to do so and start learning to build reports and dashboards the easy way. 

Dave Lessard, Sr. Business Analyst
An accomplished NAV professional with an expertise in manufacturing, distribution and payroll, Dave has over 10 years of experience in implementing ERP systems and re-engineering business processes backed by over 30 years of experience in the manufacturing industry.