Right now Power BI is definitely one of the apps to be involved in. By now, you would have heard the exciting news about Dynamics 365 being announced and how this is like the new ‘paradigm’ for bundling cloud based technologies with Dynamics CRM.
I guess if you are not excited by this, then maybe you’re excited about the latest craze – Pokemon Go (or maybe not).
This blog series will focus on how to perform JOINS on multiple CRM tables using the Merge Query operation in Query Editor for Power BI Desktop.
One of the most common needs for joins in Dynamics CRM is to show all records that don’t have any child record.
E.g. Find all Opportunities with no Notes against them.
Part 1 will focus on working with the Query Editor and getting used to some of its basic functions. So follow the steps below to get started!
Load Data Tables
This assumes that you already have Power BI Desktop installed.
Connect to your Dynamics CRM instance, and in the Navigator select the OpportunitySet and AnnotationSet tables by first searching for the table and selecting it from the list. Click on ‘X’ to return to the table list.
Repeat these steps for the Annotation table and then click on ‘Load’ for the final step.
Once the tables have loaded, you will be presented with the Visualisations page. Click on Edit Queries → Edit Queries of the toolbar to bring up the Query Editor.
This is where all the work is done to manipulate and transform data (formatting, aggregate functions, etc.). This is also the spot where we are going to perform our table joins.
So now that you have your Query Editor opened, you can see:
Left column – all your data tables (OpportunitySet and AnnotationSet) listed.
Middle column – the actual data contained in the table. Click each table on the left to display their contents
Right column – called Query Settings are all the actions performed on the table (more on this later)
AS you can see, the middle column contains many fields that we don’t care about.
To only show the columns that we want, click on Choose Columns on the toolbar to bring the column selector.
Uncheck (Select All Columns) and sort the columns by Name on the top right by clicking on the sorting icon.
Select all the desired columns or alternatively search for the ones you want and click OK.
Note that Power displays the logical name as opposed to the CRM display name!
Now that you have your desired columns, it is preferable to rearrange their order.
You can do this by clicking on the column header and dragging the column to the desired spot.
So after the clean up, I have the following two datasets:
Notice the for Opportunity table I have the column OpportunityId and for the Notes table I have ObjectId. We will need match on these 2 columns later on when we do our join.
When you’re busy removing columns and rearranging them, you would notice that these actions appear as items under the Applied Steps section on the right column.
This feature is really handy as it keeps track of all your query changes and it makes it easy for you to backtrack to a previous change — say if you would like to go back add an extra column to your table.
If you click on a particular step, it will show you the data set as a result of those.
It also gives you the option of undoing any previous change by clicking on the ‘X’ button.
For some columns like ParentContactId and EstimatedValue, you will notice that they just have a value called ‘Record’. This means that to view the actual value, you will need to expand the column by clicking on the icon on the right of the column header
So for ParentContactId, to see the name of the Contact I would just click on the Expand icon and select ‘Id’ and ‘Name’ together.
Note that you cannot just select Name by itself since it will be null – this makes sense since this is an EntityReference (lookup) data type and the field needs to have an existing GUID
I now have the table with the expanded column:
In Part 2, I will discuss the Merge Queries which will join the 2 tables together!