Improvements Add-on example: Expand and filter by collection fields

This topic describes an example of how you can expand the collections fields, filter on them and create a join for targeted reporting.

In this example, we will expand the IncidentActionPlans collection field in the Incident table and filter on the IncidentActionPlans.State and IncidentActionPlans.UpdatedDate fields.

Before you start Use the steps in Query using Microsoft Excel to create a spreadsheet mapped to the IncidentActionPlans data.

1. Open the Microsoft Excel file you have saved and open the Power Query Editor.

2. In the Queries column, right-click Reporting API OData > Duplicate.

3. Right-click Reporting API OData (2) > Rename, and type Incident.
4. In the tables list, click the Table link for the Incident table row. If prompted, click Continue. The Incident table data is displayed.
5. In the Queries column, right-click Reporting API OData > Duplicate.
6. Right-click Reporting API OData (2) > Rename,and then type Incident Action Plan.
7. In the tables list, click the Table link for the IncidentActionPlan table row. If prompted, click Continue. The IncidentActionPlan table data is displayed.
8. Right-click Incident > Duplicate.
9. Click Incident (2) and then click Home > Advanced Editor.
10. In the Advanced Editor window, paste the following text:

let
Source = Table.NestedJoin(Incident, {"Id"}, #"Incident Action Plan", {"IncidentId"}, "Incident Action Plan", JoinKind.LeftOuter),#"Expanded Incident Action Plan" = Table.ExpandTableColumn(Source, "Incident Action Plan", {"State", "UpdatedDate"}, {"Incident Action Plan.State", "Incident Action Plan.UpdatedDate"})
in
#"Expanded Incident Action Plan"

11. Click Done.
12. Click Close & Load > Close & Load. A new worksheet is added to the Microsoft Excel file and the query is displayed in the Workbook Queries column on the right.
13. Right-click on the worksheet tab and rename.
14. Click Data > Refresh All to load the latest data.