Power Query Builder

The Power Query (M) Builder is a new tool for the XrmToolBox.

It is designed to automate the basic Power Queries for getting data into Power BI from Dynamics 365. This includes selecting fields, renaming fields, querying option sets etc.

Updated: July 22 2018. This page is a work in progress and will continue to be updated.

Here is an intro video on how to use the tool, and some of its features, particularly using FetchXML in your Power Query so you can easily retrieve the option set labels, lookup names, etc.

Video is for Power Query Builder version 1.2018.7.20. (Latest version)

Like the Advanced Chart Editor, the tool was developed between Mohamed Rasheed and myself.

Features included in this tool:

  • Support for FetchXML in your Power Query
    • Show labels for Option Sets and Status fields
    • Show name for Lookup fields
    • Show name and type for Owner and Customer fields
  • Support for OData retrieval (standard querying method for Dynamics 365)
  • Auto-include GUID for current entity
  • Use Dynamics 365 display name for all columns
  • Automatically set correct data type
  • Auto-generate a link back to the Dynamics 365
  • Handle empty tables when no records are retrieved
  • Use of all-attributes in FetchXML as a setting

Check back regularly for more information about the Power Query Builder for the XrmToolBox that helps accelerate getting data from Dynamics 365 into Power BI.

Some stuff that we are still working on:

  • Include fields from related (parent) entities

Ideas, how to questions, feedback etc. please put that in the comments below.

Issues, bugs etc. please put that on GitHub: https://github.com/ITLec/PowerQueryBuilder

NuGet link: https://nuget.org/packages/PowerQueryBuilder/

A big thank you to Scott Sewell, who provided a lot of great feedback during the development of this tool. We still have items on the to-do list.

Also, we want to thank Keith Mescha and the former Sonoma Partners Power BI Accelerator. This tool showed us how to do FetchXML pagination in a Power Query. Without it, all the benefits of using FetchXML would have been limited to just 5,000 records.

As always, please sign up for my newsletter or follow me on Twitter for the latest details 

103 thoughts on “Power Query Builder

  1. Hello,

    Has anyone found a solution to connecting to CRM 2016 On Premise v.9.0? I haven’t been able to pull a query due to “DataFormat.Error: We found extra characters at the end of the JSON input. Details: Value= Position=1” along with connecting to the on API???

    Thanks,

  2. Hello,

    This tool is just great and allows miracles with Powerquery or PowerBI !

    Many many Thanks, You’re a Genius !

  3. Hi there, I was trying to follow your guidelines but I get this error message “Expression.Error: The name ‘Dyn365CEBaseURL’ wasn’t recognized. Make sure it’s spelled correctly.”. Of course, the ServiceRootURL has been copied directly from the XrmToolbox. Any ideas?
    Many many thanks in advance.

  4. Hi Ulrik,
    I’m experienced with PBI but am completely new to D365 so I’m finding your material very helpful.

    Quick question. In following this article, the FetchXML is showing columns as expected as per your example. Before becoming aware of the FetchXML method, I was using the Dynamics 365 (online) PBI connector. One of the many columns this connector showed was a ‘Table’ column named xxx_patent_account. In expanding this table column I had a list of further columns I could expand. I chose accountid as this is the field that joins the patent table to the account table (I would then go on to merge the patent and account tables). However, in following the FetchXML method, and selecting ‘Use all-attributes’, I could not find the xxx_patent_account ‘Table’ column available in order to expand it so to retrieve the joining column.
    Does this FetchXML method only allow the option to bring in display columns, and doesn’t bring in columns that are for joining purposes?

    I hope you understanding what I am trying to explain. I am still attempting to understand how D365 structures it’s database. I’m hoping soon to export the data from D365 to Azure SQL and then use T-SQL to query the data. I hope that this way the data will be more transparent.

    Thanks in advance.

    • Hi Dan.
      First I’d urge you to check out the Common Data Service connector instead of the D365 one, as that would be the one to use moving forward, if not using FetchXML.
      Also, the expand option you are referring to, is generally a very expensive option from a performance point of view, so I generally recommend never using unless you have small datasets. You can create advanced FetchXML that would query those related tables and get you the data needed. That is something that FetchXML can also do much efficiently than the standard connector, but you have to write it as a part of the FetchXML query. Check out the FetchXML Builder in the XrmToolBox, and the integration to it from the Power Query Builder if you want to look more at that.

      You can see a comparison of the different query methods here. https://crmchartguy.com/2019/07/23/comparing-the-three-main-ways-to-get-dynamics-365-data-into-power-bi/

Leave a Reply to Eric Veneziano Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s