Forum Discussion

Julian23K's avatar
Julian23K
Regular Visitor
3 years ago
Solved

oData Query To Dynamics 365 Does Not Fold

Hi!

 

I want to generate a table in Excel with Power Query. For this I need to map external (vendor) article numbers to our internal article numbers. I request the latter from our Dynamics 365 instance, buffer it with Table.Buffer() and then merge it with a bunch of other stuff. But the query takes forever to complete, because it doesn't fold and the table in Dynamics is huge.

Here is a simple example query that does not fold.

 

 

let
    Source = OData.Feed("https://xxx.operations.dynamics.com/data", null, [Implementation="2.0"]),
    VendorProductDescriptionsV2_table = Source{[Name="VendorProductDescriptionsV2",Signature="table"]}[Data],
    #"Select Columns" = Table.SelectColumns(VendorProductDescriptionsV2_table,{"ExternalItemId", "ItemNumber"}),
    #"Filter Rows" = Table.SelectRows(#"Select Columns", each [ExternalItemId] = "F000_68465165848"),
    GetMetadata = Value.Metadata(#"Filter Rows"),
    QueryFolding = GetMetadata[QueryFolding]
in
    QueryFolding

 

IsFolded is FALSE in the result. The query is so simple, how can this be?

Help would be much appreciated, since the query takes sooooo long that what I'm building is basically unuseable.

 

Best regards,

Julian

  • Julian23K's avatar
    Julian23K
    3 years ago

    Ok, this actually works and it's significantly faster. URL-length doesn't seem to be a problem.

     

    OData.Feed("https://xxx.operations.dynamics.com/data/VendorProductDescriptionsV2?$select=ExternalItemId,ItemNumber&$filter=ExternalItemId eq '" & Text.Combine(#"externalIDlist", "' or ExternalItemId eq '") & "'", null, [Implementation="2.0"])

     

    It's still quite slow though.

    Why are these "professional" Microsoft products such a mess. ğŸ™ƒ

     

    Thanks for the input!

6 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion

    Hi Julian23K ,
    buffering will stop the folding.
    Also, ignore what Power Query indicates about folding, as that might be wrong. 
    Your statement until #"Filter Rows" should fold, provided you don't buffer it.
    Just check with monitoring the web traffic.

    • Julian23K's avatar
      Julian23K
      Regular Visitor

      Thanks for the quick response.

       

      buffering will stop the folding.

      I know, I only buffer after the filtering, once I got all my data from that feed, so the query is not triggered multiple times during further processing.

       

      Also, ignore what Power Query indicates about folding, as that might be wrong. 

      That it good to know, lol.

       

      Just check with monitoring the web traffic.

      Will do. Maybe it's just slow, because our Dynamics instance is a mess. ğŸ™ƒ

      This simple example query above takes forever.

      • Shahfaisal's avatar
        Shahfaisal
        Solution Sage

        I don't believe D365 using OData supports much query folding, if any. OData is inherently slow and I strongly recommend that you use a different approach. OData is not reliable unless your data volume is really low (less than 20,000 rows in each table)

        Assuming you are using D365 F&SC (aka D365 F&O), I advise you to use Export to Azure Data Lake