For Power BI version 2.76.5678.782 (December 2019).

Drill Down enables us to select a value from a table cell. Value can be saved as a new object or query and then used in further data manipulation. Instead of selecting a single cell we can also select entire column or row.

Drill down: value

Let’s look at an example of using Drill Down. We use the following steps.

1. Open Power Query editor with Transform data.

2. Select a cell in the table.

3. Right click and select Drill Down.

4. Value is shown. Formula in formula bar = #”Reordered Columns”{2}[Date] is comprised of the last step name, row index and column name.

Power query starts counting with 0. This means index 0 represents the first line, index 1 second line and index 2 third line.

5. We could use Add as New Query instead of Drill Down in step 3. We would get a new query that still contains all the previous query steps.

Value can now be used in further data manipulation. Value is an object of type Value, but we can now convert it to object table by selecting To Table.

Drill Down: column or row

Instead of single value we can select entire column or row. We repeat the above steps. We get the cell value. We can now edit the formula.

• #”Appended Query2″{2}[Date] – value

• #”Appended Query2″{2} – 3. row

• #”Appended Query2″[Date] – column Date