Flattening Opportunity and Opportunity Products in Power BI

Colin Maitland, 08 May 2019

In Microsoft Dynamics 365 the Opportunity and Opportunity Products entities form a Header/Detail relationship. This is a common type of relationship between entities. Other examples include Quote, Order, Invoice and their corresponding Quote Product, Order Product and Invoice Product entities.

In Power BI it is common practice to flatten such Header/Detail tables into a single Fact table as shown in these images for Opportunities and Opportunity Products.

Before … Here the opportunities and opportunityproducts are two separate tables.

After … Here the Opportunities table is the combined opportunities and opportunityproducts tables.

In this article I will describe and comment on two approaches that can be used to flatten the “opportunities” and “opportunityproducts” tables into a single Fact table in Power BI using the Power Query Editor.

My sample data contains 6,066 Opportunities and 123,748 related Opportunity Products.

Approach 1 – Expand “product_opportunities” Column

The first approach is to add only the “opportunities” table to the Data Model, remove unwanted columns, expand the “product_opportunities” table column in this table, and then rename the table to “Opportunities”.

The following steps may be used to achieve this:

1.    Add the “opportunities” table to the Data Model and use a Remove Columns step to remove all the unwanted columns taking care to retain the “product_opportunities” table column required by the following step.

2.    Use an Expand step to expand the “product_opportunities” table column so that the “opportunities” table now also includes all the related Opportunity Products. Only the wanted columns from the related Opportunity Products are selected in this step.

3.    Rename the “opportunities” table to “Opportunities”.

The Data Model contains one query. When this query is refreshed, over 300 MB of data is downloaded from Microsoft Dynamics 365 and takes 30 minutes to complete. On completion, the “Opportunities Products” table in the Data Model is 12.5 MB in size.

Note: If, the columns include the “opportunityproductid” column from “opportunityproducts” then the “Opportunities” table will be 25 MB in size. This column is not required and has not been included based on the assumption that there is no requirement to identify or count distinct “opportunityproducts” records.

Approach 2 – Merge “opportunity” and “opportunityproducts” Queries

The second approach is to add both the “opportunities” and “opportunityproducts” tables to the Data Model, remove unwanted columns, merge these into a new table, expand the “opportunityproducts” table column in the new table, rename the “opportunities” and “opportunityproducts” tables, rename the new table to “Opportunities”, and then configure the renamed “opportunities” and “opportunityproducts” tables so that they are not loaded into the Data Model.

The following steps may be followed to achieve this:

1.    Add the “opportunities” table to the Data Model and use a Remove Columns step to remove all the unwanted columns.

2.    Add the “opportunityproducts” table to the Data Model and use a Remove Columns step to remove all the unwanted columns taking care to retain the “_opportunityid_value” column required by the following step.

3.    Use a Merge Queries as New step to merge the “opportunities” and “opportunityproducts” tables to create a new merged table using a join from “opportunities” to “opportunityproducts”.

 
4.    Use an Expand step to expand the “opportunityproducts” table column in the new merged table so that this table includes all the related Opportunity Products. Only the wanted columns from the related Opportunity Products are selected in this step. In this example “_opportunityid_value” column from the “opportunityproducts” table has not been selected because we already have the “opportunityid” columns from the “opportunity” table.

5.    Rename the “opportunities” and “opportunityproducts” tables; e.g. to “_opportunities” and “_opportunityproducts”. This allows the new merged table to then be renamed to “Opportunities”.

6.    Rename the new merged table to “Opportunities”.

7.    Right click on the “_opportunities” and the “_opportunityproducts” tables and turn off the Enable Load option for this table.
 
This results in those tables not being loaded or displayed into the Data Model. They are however, still used for populating the merged “Opportunities” table.

The Data Model contains three queries but only the merged “Opportunities” table is loaded into the Data Model.

When these queries are refreshed only 70 MB of data is downloaded from Microsoft Dynamics 365 and takes 3 minutes to complete.  The merged “Opportunities Products” table is 13 MB in size.

Final Comments

These two approaches for flattening the “opportunities” and “opportunityproducts” tables result in the Estimated Revenue (i.e. estimatedvalue) and Actual Revenue (i.e. actualvalue) fields being duplicated across multiple records; i.e. once for every Opportunity and Opportunity Product. As a result, when these fields are aggregated the results will be incorrect. This does not apply to similar fields, such as Extended Amount, from the “opportunityproducts” table because these fields have not been duplicated across multiple records.

In another article I will describe how to adjust the duplicated fields so that they produce correct values when aggregated.