In the Part 1 of this blog series, I discussed how to work with the Query Designer and expanding columns of data sets.

This part will describe how to join the data sets together.

Consider our example: Find all Opportunities in Dynamics CRM with no Notes against them.

Normally, in Dynamics CRM, I will do a left outer join using Fetchxml with the following query:

 

Note that there are 2 parts to this query:

  1. Create a left outer join which returns ALL Opportunities by specifying link-type=”outer”
  2. Filter out this dataset to show all Opportunities with no Note records against them (no objectid) by specifiying objectid” operator=”null”

The following steps will describe how this is done in Power BI Desktop

Merge Queries

Go back to the Query Editor as discussed in Part 1.

Click on the Opportunity dataset and then click on the Merge Queries button on the toolbar.

merge queries - join tables in power bi

 

On the Merge window with your OpportunitySet data preview, select the ‘OpportunityId’ column.

On the blank drop down list, select ‘AnnotationSet.

As for any join, you will need to select columns to join on. For our example, the matching columns are:

  • OpportunityId from OpportunitySet
  • ObjectId.Id from AnnotationSet (this is the expanded column from ObjectId)

merge window - opportunity set data preview

 

The Join Kind of Left Outer corresponds to Outer like in our Fetchxml that we discussed previously

For more information on the explanation of Join Types, see here: Stack Overflow: SQL Join and different types of joins.

After clicking on Ok, ‘New Column’ is appended to the OpportunitySet table.

Working with the  ‘NewColumn’

opportunityset table - new column

 

This ‘NewColumn’ contains the results of the query we just performed.

For each row, there is a ‘Table’ entity which holds each resulting record of the join. This is in fact a link which will take you to the corresponding linked record

Click on the NewColumn text to rename it to ‘Results’.

What we need to do now is to expand this column to show the ObjectId.Id in order to filter it to show only the resulting records that have this field as NULL, which is the second part of our query.

Click on the expand icon and uncheck Select ALL, then select ObjectId.Id.

object id

 

Click on the down arrow, and on the menu in Text Filters, select ‘Equals’.

object id 2

 

On the Filter Rows dialog, set ‘equals’ and ‘null for the drop down values.

filter rows dialog

 

This produces a result set that shows All Opportunities with no Notes against them using Power Bi!