Dynamic data sources are not supported - What is the workaorund?

[JayJay11]

Hello all,

I am trying to do the following simple query:

let
    Source = OData.Feed("https://myredacted-api.s4hana.cloud.sap/sap/opu/odata/sap/API_GLACCOUNTLINEITEM/GLAccountLineItem?$select=CompanyCode,AccountingDocument,Ledger,GLRecordType,ControllingArea,ChartOfAccounts,GLAccount,BusinessTransactionType,CostCenter,ProfitCenter,FunctionalArea,Segment,PartnerCompany,GlobalCurrency,AmountInGlobalCurrency,BaseUnit,Quantity,DebitCreditCode,FiscalPeriod,FiscalYearVariant,FiscalYearPeriod,PostingDate,DocumentDate,AccountingDocumentType,PostingKey,OriginObjectType,GLAccountType,DocumentItemText,Product,Supplier,Customer,OffsettingAccount,WBSElementInternalID,ProjectInternalID&$filter=PostingDate gt datetime'" & vDateFrom & "'", null, [Implementation="2.0"])
in
    Source

The query references another query vDateFrom which creates a string based on the current date and some adjustments:

When I try to publish this dataflow, I get an error that dynamic data sources are not supported. What is the workaround?

Please note, I also tried to solve this with a copy-action in a Pipeline, which also seems to not work (see my other post here : https://community.fabric.microsoft.com/t5/Data-Pipelines/Problem-with-variables-and-dynamic-API-quer...)

[miguel · Accepted solution]

Hi,

The alternative for your case is to use the root url of your OData service and leverage the query folding capabilities of the service and the connector. In that sense, you won't be passing the vDateFrom as part of the url for the data source (which is the thing thats blocking you at the moment), but rather you can use it as an argument for a filter in your PostingDate column. If you prefer the M code route, it's gonna be like using a Table.SelectRows and passing the vDateFrom as the argument to it.

It is not recommended to pass all the filter arguments to the url. Especially if you know that your data source supports query folding.

You can read more about query evaluation in Power Query using the link below:

https://learn.microsoft.com/en-us/power-query/query-folding-basics 

[JayJay11]

Thank you @miguel it was not on my radar that OData supports query folding, your input makes a lot of sense. I think Power Query cannot tell the user, if the query folds ("Analysis on this step was inconclusive"), but I assume it folds!

Actually, I have worked around the problem with not supported dynamic data sources to use a copy-action in a pipeline instead that creates a variable which dynamically updates the query sent. Like that, I can also store blobs in a Lakehouse which I will later pick up for further processing. And the more I narrow down the query sent to SAP, the quicker it responds.

[v-cboorla-msft]

Hi @JayJay11 

Thanks for using Microsoft Fabric Community. 

Apologies for the inconvenience.
As I understand that when you are trying to publish the dataflow Gen2 while query references another query you are getting an error dynamic data sources are not supported.

Appreciate if you could share the idea in our Idea Forum . Idea in this forum would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

Hope this helps. Please let me know if you have any further queries.

Thanks.