Power BI Tutorial: Drill into Details

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