Datasource with too many applied steps not refreshing

[michellmadeira]

Hi, 

I have this data source with too many applied steps, and can't refresh with my gateway cluster on PowerBI online service (below).

Clicking it has no effect anymore: 

Here you can have a glance at the too many applied steps I've told you so (close to 100 to be more specific):

Case:

I am connecting Pipedrive API to retrieve items (business deals in this case) snapshot, and studying the API, the only solution was to use individual calls by id using the bellow endpoint:

https://fitbank.pipedrive.com/api/v1/deals/{id}

After massive testing, I've noticed that gateway clusters do not allow data sources with dinamycally added columns (M "Table.AddColumn" with Functions, for instance), nor data sources with individual applied steps with huge instructions (M "Table.Combine" with ~500 tables).

The last alternative was to combine each table individually as specific applied steps (which turned to be ~500) regarding the data I need. By the way, with this strategy, when we have only 10 tables it worked fine, and now with just ~100 it doesn't work.

Any alternatives to this strategy or to deal with gateway cluster limitation?

Thanks in advance.

[v-eqin-msft]

Hi @michellmadeira ,

Sorry for that I could not speak the language in your screenshot.

Could you please provide the error messages in Text style to help us clarify your scenario?

In addition,  here are some troubleshooting steps why did refresh / gateway fail you can refer to. Hope it would help you.

Troubleshooting refresh scenarios - Power BI | Microsoft Docs

Troubleshoot gateways - Power BI | Microsoft Docs

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

[GilbertQ]

Hi @michellmadeira 

What I would suggest doing is to use a function which can call all the different tables. A function would have a smaller amount of steps and could loop through the data.

Here are more details: Creating a Custom Function in Power Query / Power BI — Powered Solutions

[michellmadeira]

As you see, I've already tried a function with Table.AddColumn for each IDs I have.

So you suggest calling a function that compiles all the information before rendering it in the data source?

I will try this.

[GilbertQ]

Yes I would try that it might work well.

[michellmadeira]

I've tried the following:

a) M Table.Combine with 1 function call, having 100 API calls combined, it worked

b) M Table.Combine with 10 function calls, having 50 API calls combined each, didn't work

c) My last try is to have M Table.Combine with 1 function call, having 500 API calls combined

My goal is to have 1.000 API calls compiled as a table.

Otherwise, I will change my strategy to have an Excel file on sharepoint with this table.

Edit: I've noticed that when it doesn't work, it also breaks the Gateway Cluster, demanding it to be reset.

Edit2: no chance, it just can't work: