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:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<attribute name="name" />
<attribute name="customerid" />
<attribute name="estimatedvalue" />
<attribute name="statuscode" />
<attribute name="opportunityid" />
<order attribute="name" descending="false" />
<link-entity name="annotation" from="objectid" to="opportunityid" alias="ab" <mark> link-type="outer"> </mark>
<condition attribute=<mark>"objectid" operator="null"</mark> />
Note that there are 2 parts to this query:
- Create a left outer join which returns ALL Opportunities by specifying link-type=”outer”
- 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
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.
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)
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’
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.
Click on the down arrow, and on the menu in Text Filters, select ‘Equals’.
On the Filter Rows dialog, set ‘equals’ and ‘null for the drop down values.
This produces a result set that shows All Opportunities with no Notes against them using Power Bi!