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 

136 thoughts on “Power Query Builder”

  2. 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,

    Reply

  3. Hello,

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

    Many many Thanks, You’re a Genius !

    Reply

  4. 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.

    Reply

  5. 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.

    Reply

    • 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/

      Reply

  6. Has anyone tried to use PQB to build Power BI Dataflows?
    1. My queries work in the Query Builder in PBI Desktop.
    2. When I take the advanced editor text and paste it into the dataflow window I could not get the table to resolve. I ended up hard-coding the Service Root URL in the primary query to get the dataflow to visualize in the dataflow power query window.
    3. Unfortunately, when the query refreshes I am getting this error “Error: Privacy settings don’t allow the combining of data from multiple sources in the same query. \n\r To refresh this dataflow you can either edit the queries or you can allow combining data from multiple sources in the Edit queries Options settings. Request ID: 8ece0d18-61fb-0080-75e9-c778ded55b77 Activity ID: 12d0eae8-b4d5-461d-aeff-955d6e880323”

    ANY IDEAS would save me days of work:-)

    Reply

  10. I love the Power Query Building tool and use it for our Power BI reporting in Dynamics 365 CE. However, I am trying to bring in the ‘Notes’ entity and I do not see the objectid field in the list of available fields for ‘Notes’ in the Power Query tool. Am I doing someone wrong? Is there a way to get that?

    I need to connect the notes left on entity records and bring them into Power BI … not seeing how I can do this at this time. Any advice would be helpful.

    Reply

  11. Hi Ulrik,

    I’m really getting a lot out of Power Query Builder so thanks for that!

    I came across one issue which I can’t resolve. I’m hoping you might have time to assist.

    I created a query using your tool to pull in Email records from D365
    Initially I get the error below in Power BI when I paste the query in to advanced editor

    “Expression.Error: The field ‘_from_value@OData.Community.Display.V1.FormattedValue’ already exists in the record.
    Details:
    Name=_from_value@OData.Community.Display.V1.FormattedValue
    Value=”

    This seems to be based on this section in the query
    “_from_value@OData.Community.Display.V1.FormattedValue”,
    “_from_value”,
    “_from_value@OData.Community.Display.V1.FormattedValue”,

    I tried removing the Lookup value and leaving the GUID and the “hidden” values.
    The query runs ok but the From fields all have Null Values

    I also tried renaming the duplicate entry from “_from_value” to “_from_value_hidden” which allows the query to run but again, all the ‘From’ columns have Null values

    The same is happening for ‘To’ fields.
    And the issue is also occurring when I create a query for PhoneCall records.

    Kind Regards,
    Sean

    Reply

      • Hi Cleiton,

        No, I didn’t find a resolution for this. There could be a simple solution but other priorities took over so it wasn’t something I actively pursued in the end.

  12. Hello, this tool seems a great resource based on what i’m reading and seeing! Unfortunately, i’m stuck. When running the query, I get the following message (right from the ResultsList step):

    DataFormat.Error: We found extra characters at the end of JSON input.
    Details:
    Value=
    Position=0

    I see others have had the same problem. Authentication seems to be working. I’m not a JSON developer and cannot figure out what might be the “extra characters”.

    Has anyone solved this problem?

    Reply

  13. Absolutely brilliant tool, thanks for your hard work on it!
    I have a question about some custom option sets which I can’t seem to find when they are generated. We have one which can either be against Account or Opportunity but after I’ve generated the Option Sets I don’t see it. Does anyone have any idea why?

    Reply

  15. Hi there

    Could anyone guide me on how to include a linked entity in the FetchXML builder so I can pull it into PowerBI straight?

    Cheers

    Lars

    Reply

  16. Hi Guys,
    First, many thanks for this fabulous tool. I have one suggestion for you. When I generate the query, it has step Rename before Change type. The steps should be Change type – rename. Rename should be last step. Often I want to rename the columns names and so I need to do it twice (because the change type is after rename).

    Reply

  18. Hi,
    I have used out of the Box “My Open Opportunities” view and it gives error for “Est Closed Date”. Then I tried to add any date field, still give me the following error:

    DataFormat.Error: We couldn’t parse the input provided as a Date value.

    The DAX Query is as follows:

    let
    GetResults = (z as text, x as number) =>
    let
    S = Json.Document(Web.Contents(ServiceRootURL, [RelativePath=”/opportunities”, Headers=[Prefer=”odata.include-annotations=*”],Query=[fetchXml=”

    “]])),
    P = try Xml.Document(S[#”@Microsoft.Dynamics.CRM.fetchxmlpagingcookie”]) otherwise null,
    R = if P null
    then List.Combine({S[value],@GetResults(Text.Replace(Text.Replace(Text.Replace(Uri.Parts(“http://a.b?d=” & Uri.Parts(“http://a.b?d=” & P{0}[Attributes]{1}[Value])[Query][d])[Query][d], “>”, “>”), “<", "<"), """", """), x + 1)})
    else S[value]
    in
    R,
    ResultsList = GetResults("", 1),
    ResultsTable = if List.IsEmpty(ResultsList)
    then #table(
    type table[ #"Topic"= text,
    #"Est. Revenue"= number,
    #"Est. Close Date"= text,
    #"Probability"= number,
    #"Rating (opportunityratingcode)"= text,
    #"Rating"= text,
    #"Contact (parentcontactid)"= text,
    #"Contact"= text,
    #"Account (parentaccountid)"= text,
    #"Account"= text,
    #"Potential Customer (customerid)"= text,
    #"Potential Customer (Type)"= text,
    #"Potential Customer"= text,
    #"opportunityid"= text,
    #"Email (Potential Customer)"= text ],{})
    else #"Converted to Table",
    #"Converted to Table" = Table.FromList(ResultsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1",
    {
    "name",
    "estimatedvalue",
    "estimatedclosedate@OData.Community.Display.V1.FormattedValue",
    "closeprobability",
    "opportunityratingcode",
    "opportunityratingcode@OData.Community.Display.V1.FormattedValue",
    "_parentcontactid_value",
    "_parentcontactid_value@OData.Community.Display.V1.FormattedValue",
    "_parentaccountid_value",
    "_parentaccountid_value@OData.Community.Display.V1.FormattedValue",
    "_customerid_value",
    "_customerid_value@Microsoft.Dynamics.CRM.lookuplogicalname",
    "_customerid_value@OData.Community.Display.V1.FormattedValue",
    "opportunityid",
    "opportunitycustomeridcontactcontactid.emailaddress1"
    },

    {
    "name",
    "estimatedvalue",
    "estimatedclosedate@OData.Community.Display.V1.FormattedValue",
    "closeprobability",
    "opportunityratingcode",
    "opportunityratingcode@OData.Community.Display.V1.FormattedValue",
    "_parentcontactid_value",
    "_parentcontactid_value@OData.Community.Display.V1.FormattedValue",
    "_parentaccountid_value",
    "_parentaccountid_value@OData.Community.Display.V1.FormattedValue",
    "_customerid_value",
    "_customerid_value@Microsoft.Dynamics.CRM.lookuplogicalname",
    "_customerid_value@OData.Community.Display.V1.FormattedValue",
    "opportunityid",
    "opportunitycustomeridcontactcontactid.emailaddress1"
    }),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",
    {
    {"name", "Topic"},
    {"estimatedvalue", "Est. Revenue"},
    {"estimatedclosedate@OData.Community.Display.V1.FormattedValue", "Est. Close Date"},
    {"closeprobability", "Probability"},
    {"opportunityratingcode", "Rating (opportunityratingcode)"},
    {"opportunityratingcode@OData.Community.Display.V1.FormattedValue", "Rating"},
    {"_parentcontactid_value", "Contact (parentcontactid)"},
    {"_parentcontactid_value@OData.Community.Display.V1.FormattedValue", "Contact"},
    {"_parentaccountid_value", "Account (parentaccountid)"},
    {"_parentaccountid_value@OData.Community.Display.V1.FormattedValue", "Account"},
    {"_customerid_value", "Potential Customer (customerid)"},
    {"_customerid_value@Microsoft.Dynamics.CRM.lookuplogicalname", "Potential Customer (Type)"},
    {"_customerid_value@OData.Community.Display.V1.FormattedValue", "Potential Customer"},
    {"opportunityid", "opportunityid"},
    {"opportunitycustomeridcontactcontactid.emailaddress1", "Email (Potential Customer)"}
    }),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",
    {
    {"Topic", type text},
    {"Est. Revenue", type number},
    {"Est. Close Date", type date},
    {"Probability", type number},
    {"Rating (opportunityratingcode)", type text},
    {"Rating", type text},
    {"Contact (parentcontactid)", type text},
    {"Contact", type text},
    {"Account (parentaccountid)", type text},
    {"Account", type text},
    {"Potential Customer (customerid)", type text},
    {"Potential Customer (Type)", type text},
    {"Potential Customer", type text},
    {"opportunityid", type text},
    {"Email (Potential Customer)", type text}
    })
    ,
    #"Added Link" = Table.AddColumn(#"Changed Type", "Link", each Dyn365CEBaseURL & "/main.aspx?etn=opportunity&pagetype=entityrecord&id=%7b"& [opportunityid]&"%7d"),
    #"Result" = if List.IsEmpty(ResultsList)
    then ResultsTable
    else #"Added Link"
    in
    #"Result"

    Reply

  19. I am using the XrmTool (Power Query (M) builder)
    trying to load table from D365 Eg.” User detail” and it has a column as multi select “City Visited”
    It is visible in Power BI when i use API and is also visible at Dashboard view at Power Query (M) builder
    but when searching for the column in a table it is not visible.

    do help me what can be the reason, and is there any setting which will allow me to see all the fields rather selective one

    and, have the feature of pulling columns from related table added ? it will really save our time.

    Reply

  20. Hi there
    I have a question. I would like to use the tool to show “ActivityID” from the “Activity parties” table in Dynamics 365.
    The tool doesn’t seem to show the lookup record during selection. is there a limitation to this entity? if not – can you please assist.

    Thank you in advance

    Reply

  21. I am using this Power Query Builder to generate the FetchXML as M code for my Power BI reports. I am running into an issue where the report is extremely slow, mainly because there’s a lot of data being brought in. What I would like to know is whether there is a way to filter the data before bringing it into Power BI? I didn’t see any filter options when generating the FetchXML. Please help with any suggestions on how to filter my data before bringing it in to Power BI.

    Reply

    • Yes, you can. In the advanced editor, you can modify the fetchxml, and put in whatever pre-filtering you want. Here’s an example.

      let
      GetResults = (z as text, x as number) =>
      let
      S = Json.Document(Web.Contents(ServiceRootURL, [RelativePath=”/habc_contracts”, Headers=[Prefer=”odata.include-annotations=*”],Query=[fetchXml=”

      “]])),
      P = try Xml.Document(S[#”@Microsoft.Dynamics.CRM.fetchxmlpagingcookie”]) otherwise null,
      R = if P null
      then List.Combine({S[value],@GetResults(Text.Replace(Text.Replace(Text.Replace(Uri.Parts(“http://a.b?d=” & Uri.Parts(“http://a.b?d=” & P{0}[Attributes]{1}[Value])[Query][d])[Query][d], “>”, “>”), “<", "<"), """", """), x + 1)})
      else S[value]
      in
      R,
      ResultsList = GetResults("", 1),
      ResultsTable = if List.IsEmpty(ResultsList)
      then #table(
      type table[
      #"Contract Number"= text,
      #"Contract Description"= text,
      #"Award Date"= text,
      #"Original SCD"= text,
      #"Revised SCD"= text,
      #"Original Contract Amount"= text,
      #"Change Order Amount",
      #"AMP"
      ],{})
      else #"Converted to Table",
      #"Converted to Table" = Table.FromList(ResultsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1",
      {
      "habc_contractidstring",
      "habc_description",
      "habc_awarddateactual@OData.Community.Display.V1.FormattedValue",
      "habc_originalscd@OData.Community.Display.V1.FormattedValue",
      "habc_revisedscd@OData.Community.Display.V1.FormattedValue",
      "habc_initialcontractamount",
      "habc_changeorderamount",
      "habc_siteid"
      },

      {
      "habc_contractidstring",
      "habc_description",
      "habc_awarddateactual@OData.Community.Display.V1.FormattedValue",
      "habc_originalscd@OData.Community.Display.V1.FormattedValue",
      "habc_revisedscd@OData.Community.Display.V1.FormattedValue",
      "habc_initialcontractamount",
      "habc_changeorderamount",
      "habc_siteid"
      }),
      #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",
      {
      {"habc_contractidstring", "Contract Number"},
      {"habc_description", "Contract Description"},
      {"habc_awarddateactual@OData.Community.Display.V1.FormattedValue", "Award Date"},
      {"habc_originalscd@OData.Community.Display.V1.FormattedValue", "Original SCD"},
      {"habc_revisedscd@OData.Community.Display.V1.FormattedValue", "Revised SCD"},
      {"habc_initialcontractamount", "Original Contract Amount"},
      {"habc_changeorderamount", "Change Order Amount"},
      {"habc_siteid", "AMP"}
      }),
      #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",
      {
      {"Contract Number", type text},
      {"Contract Description", type text},
      {"Award Date", type text},
      {"Original SCD", type text},
      {"Revised SCD", type text},
      {"Original Contract Amount", type number},
      {"Change Order Amount", type number},
      {"AMP", type text}
      }),
      #"Replaced Value2" = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{"Award Date"}),
      #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",null,0,Replacer.ReplaceValue,{"Original Contract Amount"}),
      #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",null,0,Replacer.ReplaceValue,{"Change Order Amount"}),
      #"Added Custom" = Table.AddColumn(#"Replaced Value4", "Contract Total", each List.Sum({[Original Contract Amount], [Change Order Amount]})),
      #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Contract Total", Currency.Type}}),
      #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Completion Date", each if [Revised SCD] = null then [Original SCD] else [Revised SCD]),
      #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Original SCD", "Revised SCD", "Original Contract Amount", "Change Order Amount"}),
      #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"""""",Replacer.ReplaceValue,{"Completion Date"}),
      #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Contract Number", "Contract Description", "Contract Total", "Completion Date", "Award Date"}),
      #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Completion Date", type date}, {"Award Date", type date}}),
      #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Award Date", Order.Ascending}, {"Contract Number", Order.Ascending}}),
      #"Reordered Columns1" = Table.ReorderColumns(#"Sorted Rows",{"AMP", "Contract Number", "Contract Description", "Contract Total", "Completion Date", "Award Date"})
      in
      #"Reordered Columns1"

      Reply

  22. I have a problem getting the value for party list fields using this tool. Should this be possible? I try to get Phone Calls information and I can’t get the data for the To and From fields in power bi.

    Reply

  23. Hi Wonderfull tool ! I Just notice one ennoying detail, the tool is using logical name to build the Odata query. but powerbi is using the schema name . And of course, i,n my environment the logical name is all lowercase and the schema name is CamelCase :/ any idea of how to fix this ?

    Reply

  24. Hi Wonderfull tool ! I Just notice one ennoying detail, the tool is using logical name to build the Odata query. but powerbi is using the schema name . And of course, i,n my environment the logical name is all lowercase and the schema name is CamelCase :/ any idea of how to fix this ?

    Reply

  25. I would like to know, what would the syntax be that I would need to use, if I wanted to pass in some kind of variable to the fetchxml filter? P.S. I’ve tested using this in Excel, which is why I ask. What I’d like to do, is have a cell on the worksheet, where they enter a value. When they click to refresh the data, that value is passed into the query, filtering it. I do not even know how to begin that process, so any help at all would be great.

    Reply

  26. Hi Thanks for video is very good. Short question xmltoolbox only work for dynamics CRM ? I would like to know if work for Dynamics D365 Finance and operations ?

    Thanks
    J

    Reply

  28. Hi! Thank you for this great tool. I had a go at it and loaded an invoice view from CRM in Power BI, but unfortunately I’ve encountered an issue. It’s missing some invoices. Even though in CRM the invoices show up in the results, in Power BI they disappear (I don’t have any filters in the XML script). Has anyone encountered this? Any idea why? Any help is much appreciated.

    Reply

  29. Hi,

    How to navigate to record ID on which change was performed once we extract the Audit table?
    We can see what was changed and on which table but it seems it doesn’t catch the ID of the record from the original table…
    Any of guid which is returned in output doesn’t refer to the table record guid, where the change was done.
    Any advice?

    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 )

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