8 Reasons to Move From Excel Reporting to Power BI

Microsoft Excel has traditionally been the go-to reporting tool for businesses, but Power BI offers powerful analytics and reporting features for organizations. With faster experimentation with visualizations, statistical functions and calculations across broad datasets, and ability to derive answers on the fly through the rapid recombination of fields, it is clear that Power BI delivers far greater insight than Excel. Let’s take a look at how Power BI and Excel compare.

1. Access and Store a Vast Amount of Data (with No Issue)

Power BI has very impressive compression abilities for Microsoft Excel and text/.csv files, which allows you to view, analyze and visualize huge quantities of data that cannot be opened in Excel. Most computers would struggle to open up a 300 megabyte .csv, making analysis and reporting in Excel a challenge. Adding several smaller .csv files to data, like sales data for neighboring regions, will be immensely difficult to combine with your previous dataset and analysis.

Can you imagine having all your files stored, connected, thoroughly analyzed, and using only 50mb? That’s now a reality with Microsoft’s Power BI, which uses powerful compression algorithms to import and cache the data within the .PBIX file.

Large datasets don’t have to be cut down in size and aggregated to show more encompassing analysis. All the most granular details and summarizations can exist in one file together and be accessed through drill downs.

You also have some ability to modify and prepare your data as well:

• Transformations and edits including changing formats, deleting rows, adding columns, and transposing data.
• Adding relationships within different tables, which is useful if you have a star or snowflake schema.
• Adding new datasets easily as additional useful data is discovered. New data can be integrated on Power BI without needing to recombine everything else again. This gives you huge flexibility in linking datasets together.

With Power BI, painful repetition of data preparation steps is reduced or outright eliminated since the software will remember the data preparation steps and automatically repeat them during data refreshes.

2. Spot Data Trends Quickly and Easily

Analyzing trends with Power BI is extremely easy. With built-in time intelligence features, it only takes seconds to view vast amounts of data. You can view data by various dimensions and attributes, including date-time dimensions.

If you don’t have the date in a schema structure, you can easily create a date dimension table in seconds to allow the time intelligence functions to work. As an analytics consultant, I have personally met business executives who spend hours every month writing Excel macros across various large files to get comparisons in the form of a time series. These executives work across several large Excel files, be it through using VBA or other coding methods.

Creating a time trend analysis in Power BI

1. Firstly, a date dimension table needs to be created. Use the “New Table” function within the Modeling ribbon of the Power BI Desktop, and set it as Date = calendarauto(). This creates a table with all the dates that exist for your dataset. Note that this is not possible if you are doing a DirectQuery into your data.
2. Next, the relationship between your data and the date table needs to be created. First ensure your data’s date field has the correct formatting in Query Editor. In the relationships tab of Power BI on the left hand side, drag the date fields from the date table to the date field in your data. Note that the date dimension table can be connected to multiple tables.
3. Going back to the report tab, you can now create a visual using the date on the x axis, and the measure (revenue, for example) as the value.
4. Additional useful measures can be created using the time intelligence functions of Power BI, like year to date revenue. By right clicking on the table, selecting a new measure, and entering it in the below format, the new measure can now be plotted on graphs like any other measure. A year to date measure gives a more accurate comparison of performance between the years: YTD_Measure = TOTALYTD(sum(Table_Name[Value]),Dim_Date[Date])

3. Intuitive UX Features

The beauty of Power BI is how easy it is to make something visually appealing and neat with limited creativity or UX skills. Drag-and-drop functionality, easy resizing, and copying and pasting makes those who are familiar with the Microsoft Office suite feel right at home.

Creating consistent branding and color schemas has never been easier than now with Power BI themes. An organization’s branding department can now create a simple Power BI branding theme, in the form of a .json file, and each analyst has only to import this theme during development. From there they will all be creating graphs and charts that are consistent with one another in terms of colouring.

A theme is simply a .json file that has the various colours in HEX format within them and can be created or modified within a notepad file. Through the themes tab on the Home ribbon in Power BI, this .json file can be imported. More information on creating themes can be found on the Microsoft website at this link:

https://docs.microsoft.com/en-us/power-bi/desktop-report-themes

Additionally, Microsoft’s legendary “Format Painter” will copy all formatting (like color and font) across similar visualizations. The ability to make format changes to individual visuals, like turning markers on for line charts and modifying the placement of data labels, allows people to create custom format changes so that they are more visually appealing and easier for dashboard viewers to follow.

4. Incredible Cloud-based Features

Once you have finished constructing your Power BI analysis, you need a way to publish and disseminate your dashboards and reports. In Excel this would typically involve emailing a large file around, putting it in a shared drive or SharePoint portal, and letting people know it has been updated. Now with Power BI, this process has been completely revolutionized.

We now publish to the secure Microsoft hosted cloud service, called Power BI Service, and automate the refreshing of our underlying data. Hitting publish at the top right of the Power BI home ribbon is all that is needed.

If you need more reason to use the Power BI Service, other than streamlined publication and distribution, there are a handful of valuable tools that are available once we publish to the cloud:

• Quick Insights: powerful algorithms running off the cloud can quick insights straight out of your dataset. By right clicking your dataset on the Power BI Service and clicking quick insights, you can get a thorough analysis of your data without even having to open it in Power BI Desktop or build any dashboards. You will need to have a data model created, with the necessary relationships if needed.
• Natural Language Query: This incredible feature allows users to type questions and get an answer, either in a default or user-specified form. This is useful for executives with specific questions and business users who are unfamiliar with Power BI or the data model behind the report. Additionally, this feature saves the report developer from having to create dozens of extra visualizations onto the dashboard in order to answer every potential question. To access this, just type a question into the query box on the relevant dashboard.
• Personalized Dashboards: Power BI users can create dashboards that can host individual visualizations from various different reports, or whole reports itself. Here, users have the ability to customize the size and layout of various visuals, and lay out the dashboard in a way that is most insightful to their specific role. Dashboards can even be created using visualizations that are connected to different data sources. These newly made dashboards can also be shared with other users within the organization for further collaboration. To create or move a visual to a dashboard, simply click the pin button on the top right of a visual in a report.
• Alerts: Once a dashboard has been created, email alerts can be set up on KPIs. By right clicking on the visual on the dashboard and selecting “Manage alerts”, the option to set up alerts pops up. This can be useful for employees tracking a specific measure as part of a trial process, or if inventory falls below a certain amount.

5. Row Level Security Features

Adding Row Level Security (RLS), something this was previously complicated in Excel, can now easily be done for different groups by non-coders. A good of example of using RLS is to ensure that employees can only see data relevant to their local geography. Adding these access filters is simple, and the risk of others seeing data they shouldn’t from mailing around an Excel file is eliminated. To set up RLS, the filters for different “roles” are set up within Power BI, and the individuals/ groups are added to the roles through the Power BI Service online.

To set up RLS, select “Manage Roles” under the Security tab of the Modeling ribbon. Here you can create roles for different groups, using DAX expression filters for different fields. For example, the role for Canada will have the country filter set to Canada.

Once the report has been published to the Power BI Service, individuals or Office 365 groups can be added to different roles, though the Security option when right clicking on a dataset.

Conclusion

Power BI is an easy tool to use that helps propel organizations into a data driven culture. With powerful self-service abilities, no longer are business users dependent on IT for gathering, transforming and analyzing data. Automation of dataset integration, ease of expansion into new data sources, rapid visualizations, and easily deployable Row Level Security are amongst the many reasons Power BI is a necessity.

Additionally, a cloud-based collaboration environment with features like Natural Language Query ease the process of data governance. In the age of analytics, it’s import for organizations to intelligently utilize the data they collect to rapidly evolve with changing business and consumer habits.

The future is bright and shiny for analytics – companies are swarming to incorporate data visualization, and to do away with tabular reports with huge reams of numbers. With some quick retooling, you too can be a Power BI super star for your organization!