Dataflow refresh fails without error message - second API call later in query

[mm5975]

Hi everybody,
I am creating a new dataflow, that connects via API to get a list of entities and then for each entity id gets data via API using RelativePath:

let
  Source = Json.Document(Web.Contents("https://api.businesscentral.dynamics.com/v2.0/xxxxxxx/api/v2.0/companies")),
  #"Converted to table" = Table.FromRecords({Source}),
  #"Expanded value" = Table.ExpandListColumn(#"Converted to table", "value"),
  #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"id","name"}),
  #"Added Custom" = Table.AddColumn(#"Expanded value1", "VLE", each Json.Document(Web.Contents("https://api.businesscentral.dynamics.com/v2.0/xxxxxxx/api/hougaard/SOD/v2.0",
[
RelativePath = "companies("&[id]&")/data"
]
)))
in
  #"Added Custom"

Preview in Power Query online editor looks fine, however when I save the dataflow and refresh it, after a while it fails. When downloading details about failed refreshed, the text files looks like this, without any specific error message:

Requested on,Dataflow name,Dataflow refresh status,Table name,Partition name,Refresh status,Start time,End time,Duration,Rows processed,Bytes processed (KB),Max commit (KB),Processor Time,Wait time,Compute engine,Error,
2024-01-24 10:39:33,DataflowName,Failed,DataflowName,NA,Failed,2024-01-24 10:39:33,2024-01-24 10:40:04,00:00:30.4530,NA,NA,NA,NA,NA,NA,Error: Request ID: 172e995d-9fd6-7216-213e-89498f16049d Activity ID: 2414244c-c262-4fec-b5f3-9c1279181799

When I remove the "#Added Custom" step from power query (see below), the refresh succeeds:

 let
  Source = Json.Document(Web.Contents("https://api.businesscentral.dynamics.com/v2.0/xxxxxxx/api/v2.0/companies")),
  #"Converted to table" = Table.FromRecords({Source}),
  #"Expanded value" = Table.ExpandListColumn(#"Converted to table", "value"),
  #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"id","name"})
in
  #"Expanded value1"

Can you please advise me, where the problem might be? 
Thanks a lot!

UPDATE 25/01/24: Dataflow fails also when not using relative path, just another API call:

let
  Source = Json.Document(Web.Contents("https://api.businesscentral.dynamics.com/v2.0/xxxxxxx/api/v2.0/companies")),
  #"Converted to table" = Table.FromRecords({Source}),
  #"Expanded value" = Table.ExpandListColumn(#"Converted to table", "value"),
  #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"id","name"}),
  #"Added Custom" = Table.AddColumn(#"Expanded value1", "VLE", each Json.Document(Web.Contents("https://api.businesscentral.dynamics.com/v2.0/xxxxxxx/api/hougaard/SOD/v2.0/companies(xxxxxxx)/data")))
in
  #"Added Custom"

There is still no error message after failing.

When creating new dataflow with just this second API call, it works fine and refreshes without errors:

let
  Source = Json.Document(Web.Contents("https://api.businesscentral.dynamics.com/v2.0/xxxxxxx/api/hougaard/SOD/v2.0/companies(xxxxxxx)/data")),
  #"Converted to table" = Table.FromRecords({Source})
in
  #"Converted to table"

So the problem with refresh is only when using second API call in the query.

[Anonymous]

Hi @mm5975 ,
Based on your description and the code you provided, the problem may be in the query folding. The "Added Custom" step might be preventing query folding, which is the process of offloading the data transformation workload to the source system. Non-foldable steps can cause inefficiency and may lead to refresh failures. When you remove this step, query folding might be restored, allowing the refresh to complete successfully.
So please ensure that steps prior to the custom column are foldable. Try to push as much logic as possible to the source system. Please refer to this article for more details on query folding.
Query folding - Power Query | Microsoft Learn
Apart from this， you can add error handling in your M query to manage possible issues with the API calls. This could involve using constructs to capture and handle errors gracefully.(like try ... otherwise)

To help with debugging, you can try breaking down the "Added Custom" step into smaller parts and refreshing the dataflow at each step to isolate the point of failure.

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mm5975]

Thank you for your reply!
As I understand, query folding is used e.g. with databases, where query can be translated to native querying language of the database (e.g. SQL). In case of API call, I don't think there is a way to "push logic the source". I also believe the data in source system (MS Business Central) has the standard form with multiple companies used and it is not possible to change the data structure. 

I tried error handling (try...otherwise), but it did not help. When in power query online editor, the preview looks fine and the API call returns the data witout errors for each company. It's just that after saving and refreshing a dataflow, it fails unexpectedly and witout error message.

Breaking the "Added Custom" step into smaller parts is not possible, it is just an API call that could not be more simplified...

Best regards,

mm5975