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 

37 thoughts on “Power Query Builder”

    • Hi Olivier. We are adding support for more methods soon. We will definitely have a close look at CDS-A when it becomes fully available. Right now it looks like you’ll be able to use the same query string that is generated., but we’ll have to wait a little and see.

      Reply

  3. The Power Query builder is now used in production. I handed over a PowerBI report to a client today where the last extra data source I had added was made with your tool.
    Besides the ideas you have already mentioned I would add: pre-filtering in the builder for instance: “only active accounts”.
    And there is an issue when selecting the statuscodename field. I can export the query but it will error in Power Bi and say that there is no statusccodename. I just changed it to statuscode and got the usal codes. Just thought I could get the name now the field was on the list.

    Reply

    • Hi Marianne. Thanks for the input. The name field issue you are referring to was because the tool included virtual fields. OData can’t support those. The latest version automatically excludes those from the list.

      Reply

  4. Will this work for me if I’m on prem and version 2015? I am getting errors and can’t find any version limitations listed on your docs. Thanks!

    Reply

    • Good question. I have not been able to test that, but the tool automates the transformations based on the Dyn365 metadata. Those are the same between online and on-premise, so the only manual adjustment you may have to do for an on-premise environment, is the ServiceRootURL and make sure that is in the proper format.

      Reply

    • Hi Rajul. Yes, it works for 8.2 as well. However, it does require that you are using a recent version of the Power BI Desktop, so if that is more than 2-3 months old, you may have to update. Just as a note, future versions of the tool will have features that are only available for 9.0 and above, but the current/standard feature set will continue to work.

      Reply

      • Hello, thanks for the response. I need data for audits. It seems to be working for 9.0 but doesn’t work for 8.1 instance. Do you have any idea why it isn’t working?

  12. hi
    thanks for the great tool.
    please add a part to accept custom FetchXML queries and not only accept views
    i need to create a fetchXML Main query from a complex query i generated by FetchXML Query Builder
    Thanks,
    Hassan

    Reply

  13. Hi,
    This tool is fantastic, a huge THANK YOU to you and your contributors!!
    I pulled a FetchXML query that had “OR” Filters across two different Entities, the result pulled through multiple of same records, I couldn’t see a Distinct=True in the Generated Query so added it in front of Page code (

    Reply

  14. This is a game-changer for me and my clients. Much more elegant way to pull in Dynamics CRM data and transform data in Power BI. Really impressed and hope you keep developing this awesome tool!

    Reply

  15. Has anyone else experience issues with custom date fields not updating when using Power Query Builder?
    I have a bunch of custom date fields on my Opportunity entity and for some reason they are not updated in Power BI.
    Other standard date fields as est. close date etc. are updated as supposed.

    Reply

  16. Follow-up on my last post.

    Sometimes I get an error instead:

    DataFormat.Error: We couldn’t parse the input provided as a Date value.
    Details 2018-10-31T07:00:00Z

    I’ve tried messing around with the Date/time field settings in Dynamics and also the data type in Power BI without any luck.

    I’m not able to spot any patterns.

    Reply

  17. Hi Ulrik,

    Loving the tool. however, when trying to use the new “Copy FetchXml” functionality I get and error when adding a query from the asyncoperation table (System Jobs).
    I have exported this query from CRM, used the “Open Fetch Xml Builder”, opened the query and ran successfully. When using the “Return FetchXML” function PowerQuery fails with the following message.

    “System.Exeption: Error while retrieving views: Object reference not set to an instance of an object……

    Reply

  18. Hi,
    We have working Queries in PBI Desktop (using Power Query Builder), however after Publishing to PBI Online and setting up a Scheduled Refresh (Using the On-Premise Data Gateway) we get an error:
    “We found extra characters at the end of JSON Input.”

    Is anyone else experiencing this? Does the Json query need to be different for Web version of PowerBI??

    Thanks
    Clayton

    Reply

    • Hey, I’ve heard this reported before, but haven’t seen it myself. Let me know if you locate the issue, and we’ll try to update the tool if possible. I do think it is specific to on-premise, as I have not seen issues with online. Will definitely share any findings on this if they come along.

      Reply

  19. Hi Ulrik

    I just updated to version 1.2018.10.29

    Custom Picklists are not being generated correctly through the “Generate OptionSets”. Status and StatusReason works and standard option sets – but not custom picklists.

    Error: Expression.Error: Vi kan ikke anvende feltadgang på typen Null.
    Detaljer:
    Value=
    Key=Options

    In the first step “source” the OptionSet is null

    For optionsets that work is says Record.

    Reply

  20. Hi Ulrik,

    Love the tool! I just updated to 1.2018.1014.5 and when I Generate FetchXml I no longer have the Filter pane to filter the data. Has this been removed or can be accomplished in another way?

    Thanks

    Eric

    Reply

  21. Hi Ulrik

    How do you recommend getting the Process Stage on Opportunity into PowerBI – and sorted?

    If I use Stepname/Pipeline Phase – it is prefixed with a number – and the field does not always seem to be up to date in CRM. – Also the same phase can have different numbers and some phases can have the same number. It is a CRM system with many iterations of the BPF…

    If I use a view for Opportunity and include the Stage Category from the Process Strage – the view will have data in CRM, but not Power BI if I use Power Query Builder.

    So I include the PBF entity for opportunity as a table. This works fine.
    Then I include the BPF entity for lead as a table.
    Then I combine the two in a merged table so I can link to both lead and opportunity.
    Then I add a new coloum (from example) and add the sort numbers of the stages. And then I sort the Active Stage coloum by the new sort coloum.

    This works.

    But the question is – is there an easier way to do it? How do you get the Process Stage into Power BI?

    Reply

  22. Hi Ulrik,

    What a bloody brilliant tool 🙂 As a business person and not a data analyst I am challenged to find ways to improve the views and dashboards beyond what is natively possible in Dynamics. I have been using your guide to update the charts to make them really useful and now discovered this wonderful tool. But again, it would still be way beyond me if not for your incredible ‘tutorials’ that take things which are relatively sophisticated and make them seem much easier.

    Big thanks!

    Reply

Leave a Reply

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

Gravatar
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 )

Cancel

Connecting to %s