Power BI Tutorial: Drill into Details

No comments

Power Query has a feature that can be used to drill into details. This is particularly useful when working with 32 bit excel.

Sample workbook can be downloaded here.

Here are the steps.

  1. For the purposes on the tutorial let’s load Sales data from an excel table (this could be from a database). Highlight Sales table, click on the ribbon -> Power Query -> From Table to create a query. Load and Close the query

  2. Create an excel table called Product. This table will be used to filter Sales table, hence reducing the number of rows

  3. Highlight Product table, click on the ribbon -> Power Query -> From Table to create a query. Load and Close the query
  4. Edit Sales query. From the ribbon select Merge Queries

  5. Select Product from the dropdown
  6. Select “Only include matching rows” and select OK. This will merge Sales data with the Products and selects only those rows that are matching. Hence we are filtering Sales table to include only rows that have Shoes and Socks as Products
  7. Remove the NewColumn
  8. Load and Close the query
  9. To change the filter, change the Product table in excel to Hat and Shirt

  10. Refresh Sales query and the new filter is applied

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s