New Power BI Explore this Data Feature: An Alternative to Excel Pivot Tables

By:   |   Updated: 2023-12-18   |   Comments   |   Related: > Power BI


Problem

For years, many Power BI end users have had to move their data from Power BI to Excel for further ad-hoc analysis. In my experience, I have found that this is the case mainly because they find it easier to use Excel Pivot tables for analyzing their data even when they are not very technical at manipulating the data or understanding the modeling development process in Power BI. Does Power BI have a feature that let's you pivot and analyze the data?

Solution

The new "Explore this data" feature in Power BI service allows end users to perform their analysis within Power BI without exporting the data to Excel first or connecting to the Power BI semantic model from Excel.

This tip will describe how this can be achieved. Please note that this new feature is still in preview, and we can expect more updates to be added in time for even more usage.

How Can I Locate this Feature?

First, you must log into the Power BI service. Select the workspace where you have the dataset (Semantic model), then click on the ellipses () to reveal this feature, as shown in the image below.

Snapshot showing how to navigate to the Explore this data feature in Power BI service.

Click Explore this data, and a new window will open:

Snapshot showing the Explore this data feature window in Power BI service.

Below is the same view in an Excel Pivot table:

Snapshot showing Excel Pivot table window.

How Do I Use It Like a Pivot Table?

As you can see in the image above, like a pivot table view, you have the dataset on the right-hand side of the screen. You only need to drag the fields from the queries (tables) into the empty canvas. For example, I am going to pull "Month" from the "Date" table into rows and "Sales" from the "Sales" table into values. See the image below.

Snapshot showing how to use the Explore this data feature window in Power BI service.

The same view with the same fields and values in the Excel Pivot table is shown below.

Snapshot showing how to use the Excel pivot table window.

In the following image, we can add the "Country" field from the "Geography" table to the column well on the Power BI side.

Snapshot showing how to use the Explore this data feature window in Power BI service 2

You would have something like the image below when completed in the Excel Pivot table:

Snapshot showing how to use the Excel pivot table window 2

Can Filters Be Added to the Power BI Visual?

Like in a Pivot table in Excel, we can also add a filter to the visual. In this example, I have added a year filter using "Year" from the "Date" table. Follow the steps below.

Click on "Add filter," select "New Filter," click on the dropdown, and expand "All data." Next, choose the table that contains the needed field (in this case, it's the "Date" table), and then select the field name.

Snapshot showing how to add a filter to the Explore this data feature window in Power BI service.

The new filter will appear at the top left and have selections like what you find in a Pivot table filter (see image below). You can always add more filters if you wish.

Snapshot showing how to add a filter to the Explore this data feature window in Power BI service 2

Filters on a Pivot table look very similar and are also located at the top left, as seen in the image below.

Snapshot showing how to add a filter to an Excel Pivot table.

What Other Things Can I Do with this Feature?

In addition to showing values on a matrix like a Pivot table, you can view the data in other types of visuals applicable to your needs. Let's see how this works.

First, click on the arrows at the bottom right highlighted in the red box.

Snapshot showing how to expand the visualisations view in the Explore this data feature window in Power BI service.

This reveals a visual for the fields used in the initial Matrix visual. You can change the visual to your preference in the area highlighted below.

Snapshot showing how to change type of visualisation in the Explore this data feature window in Power BI service.

After completing the data pivoting, you can save your work by choosing "Save" as a pivot or "Save as report," as seen below. You can also "Share" the pivoted report with others in your organization, as seen in the top right corner of the image below.

Snapshot showing how to save exploration in the Explore this data feature window in Power BI service.

Current Feature Limitations

  • You will need access permissions on the dataset (semantic model) you are exploring.
  • Currently, you can only save your work on a workspace with premium capacity.
  • Depending on what workspace you are saving your work (My workspace or an Enterprise workspace), you may need to refresh your page to see the created artifact if you're using "My workspace."
  • If you change the sensitivity label on your Semantic model (dataset), it does not currently move to the exploration you created it on.
  • This feature is currently not supported for streaming datasets.
  • A Pro or Premium Per User (PPU) license is required to share explored data.
  • You need to grant your end users build permission to the underlying dataset; otherwise, they cannot view the exploration.

In summary, the new "Explore this data" feature is still in preview, so expect more functionalities to be added over time. Also, as with everything in Power BI, there is always more than one way of doing things, like accessing this feature from within an existing report page and a Semantic model page as "Explore this data."

Comment below to share how you use this feature apart from using it as an alternative to using Pivot tables in Excel.

Next Steps

Learn more about Power BI in this 3 hour training course.


Click here to start the Power BI course






sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelorís and masterís degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-12-18

Comments For This Article

















get free sql tips
agree to terms