Using the Power Query (M) Builder for XrmToolBox to Generate M Code for Dynamics 365

In this post, we will look at using the Power Query (M) Builder in XrmToolBox, developed by Mohamed Rashid and Ulrik “CRM Chart Guy” Carlsson. This tool is a great way to quickly help build Dynamics 365 reports in Power BI. It generates M queries with and without FetchXML as well as handles fields such as option sets.

To install it, open the XRMToolBox and find the Power Query (M) Builder app. Click Install:

Next, connect to an organization and open the app:

You will see:

Click Load Entities:

This will display a list of entities. Select Account. This will display account fields:

First thing we will need is to generate the Service URLs for Power BI. These will be the base URLs that our queries will use. Click Generate Service URLs to generate 2 URLs:

Now open Power BI Desktop. Select Blank Query:

Now copy the Dyn365CEBaseURL into the blank query and rename the query Dyn365CEBaseURL:

Now create another blank query and this time copy the ServiceRootURL:

Let’s add some fields to our query from Select Table and Columns tab by selecting the fields on the left and clicking the ==> arrow:

Now let’s generate the FetchXML for this query. Click Update FetchXML. You will see a list of the FetchXML fields:

Note the accountid field is added automatically. Clicking on it, we can move it to the top:

Let’s select Add Record URL as well. This will provide a link to the record back to Dynamics 365:

Now click Generate FetchXML. This will generate the FetchXML:

In Power BI, create a new query and select Advanced Editor:

Paste in the code from the generated FetchXML:

Click Done. You may need to reauthenticate:

The query will then be loaded:

Note the Classification field is an option set, and with this query we get the option set number and label:

As we checked Add Record URL we get a field with the record link:

Click Close and Apply:

We can then build our report using this query:

Let’s add the Link URL. At this point it is displaying just the text of the link:

We can change this field to a web URL. Select the field and under Modeling change Data Category to Web URL:

This now displays the URL as hyperinks. We can then change it to an icon by selecting URL Icon under the Format options of the field:

Clicking one of these links opens Dynamics 365:

Back in the XrmToolbox, click Generate OData so we can compare between using this and FetchXML:

The M Query to retrieve these fields is generated:

Note the fields have been renamed as well:

let
    Source = OData.Feed(ServiceRootURL, null, [Implementation="2.0"]),
    entity_table = Source{[Name = "accounts", Signature = "table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(entity_table,{"name","telephone1","primarycontactid","emailaddress1","address1_city","accountclassificationcode"}),
    #"Renamed Columns" = Table.RenameColumns( #"Removed Other Columns",{{"name","Account Name"},{"telephone1","Main Phone"},{"primarycontactid","Primary Contact"},{"emailaddress1","Email"},{"address1_city","Address 1: City"},{"accountclassificationcode","Classification"}})
in #"Renamed Columns"

Copy the query into a blank query in Power BI Desktop. It will look like below. Note the Primary Contact is not expanded and the Classification field is only the number value, not the label:

In the tool, let’s click Generate OptionSets. This gives us a query of our Classification optionset:

The query in Power BI brings in the value and label:

For more information, visit https://crmchartguy.com/power-query-builder/.

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.com | LinkedIn | Twitter | YouTube

Facebook

Twitter

Linkedin