How to Use Excel PivotTables & Power Bi
/
Having the ability to look at business data in a meaningful way is important for finding insights. Being able to identify a downward trend and address it or capitalize on a positive data point can help businesses grow and improve profitability.
Company leaders that use data visualizations and advanced analytics are 5x as likely to make decisions faster than their peers and 3x as likely to properly execute those decisions as intended.
Last year, Microsoft made it easier for companies to create meaningful data analytics by combining PivotTables in Excel for the web with its business intelligence dashboard Power Bi.
Let’s look at each of those pieces and then how they combine to make data analysis easier and more powerful at the same time.
What is an Excel PivotTable?
A PivotTable in Excel is a tool that allows you to calculate, summarize, and analyze data. It helps reveal data patterns and trends.
The PivotTable will pull in data from other resources and group it together into a single place, where you can organize that data in multiple ways and use graphs to create visualizations.
Using PivotTables, you can look at a large amount of data in a user-friendly way, have calculations run on that data in the background, and more.
Some of the things that PivotTables can do are:
Subtotaling and aggregating data
Summarizing data by categories and subcategories
Creating custom calculations and formulas
Expanding and collapsing levels of data
Making it easy for you to drill down into different summaries of source data
Filtering, sorting, grouping, and conditionally formatting data
Providing attractive and annotated reports
What is Power Bi?
Power Bi is an application that connects to multiple data channels throughout your organization to bring all your business data into one place.
The platform has over 500 free data connectors that make it simple to connect to many different data sources, like Azure SQL, Salesforce, SharePoint, Excel, and more.
The platform allows you to create multiple interactive and visually engaging reports that are easy to share with others.
Why Bring Power Bi Data Into Excel?
Excel has been around for ages, and it is the “go-to” tool for a lot of organizations and their teams. While Power Bi gives a company the ability to bring all its online data sources into a single platform, Excel provides an easy user experience that people tend to be comfortable and familiar with.
It also has features, such as PivotTables and data types that allow you to do more with your raw data.
How to Create an Excel Pivot Table Using Power Bi Datasets
Step 1: Insert PivotTable
First, go to Insert in the top menu and click the PivotTable. Then choose From Power Bi (Microsoft).
Note, that your company must already have Power Bi in your Microsoft plan for this option to be enabled.
Step 2: Chose the Dataset You Want
Next, in the Power Bi Datasets pane, you’ll choose the dataset that you want to bring into Excel. When you do this, a PivotTable will be created for you in a new spreadsheet of the workbook you’re in.
Step 3: Add Fields to Your PivotTable
Now, you’ll want to begin building out your PivotTable by adding the fields you want to work with from your dataset. The way that Excel creates the PivotTable is to add the fields that you select to Rows and to add any date and time hierarchies to Columns. Numeric fields are added to Values.
You can move fields from one area to another by dragging the field.
Refreshing Your Data
Why connect data in an Excel PivotTable to Power Bi? Because the data can be live, and thus be updated as it comes in.
For example, say that you have sales and lead data from Salesforce connected to Power Bi. You could bring this into an Excel PivotTable and never have to worry about asking the sales team for the daily sales figures again. The data would refresh automatically as updates and new data are added to your Salesforce account.
To refresh your data inside Excel to capture any updates, you can use the following steps:
Click anywhere in your PivotTable to show the PivotTable Tools on the ribbon.
In that Tools area, click Analyze > Refresh.
You can refresh data automatically when the Excel workbook is opened by doing this:
Click Analyze > Options
On the Data tab, check the box for Refresh data when opening the file
Get Help Incorporating Technology Solutions to Boost Your Business
Knowing how to use technology to make things easier and get better business insights is important if you want to be competitive. BrainStomp can help your business with smart technology solutions that improve your bottom line.
Schedule a free consultation today! Call 260-918-3548 or reach out online.
