topic Re: Table.Combine in parallel possible? in Power Query

Table.Combine in parallel possible?

BartVW — Tue, 10 Sep 2024 12:01:21 GMT

Hello colleagues

To work around a constraint pulling data from our source system, I am splitting up the data pull into 3 queries that all have their date filter criteria. I then append them all into a single table to be used in my model.

The 3 sub queries have 'enable load' disabled to avoid loading the data to my model twice.

Monitoring the query execution, it seems that table.combine executes the 3 sub queries one after another.

Does anyone know of a way to do this in parallel, so that it is faster?

Thank you

Bart

Re: Table.Combine in parallel possible?

lbendlin — Tue, 10 Sep 2024 16:39:57 GMT

Where are you monitoring this, in Power BI Desktop or in the service?

Re: Table.Combine in parallel possible?

BartVW — Wed, 11 Sep 2024 07:12:46 GMT

I am monitoring this in the source system (SAP). FYI we are using an OData service based on SAP data to pull in the data.

In that SAP monitor, I can see only the first subquery executing, and once it finishes it starts the 2nd one in the Table.Combine list of tables, etc.

If I keep 'Enable Load' enabled on the subqueries, then I see 4 queries hitting SAP at the same time: the 3 subqueries and the first subquery in the Table.Combine list.

Interestingly, the remaining 2 subqueries in the Table.Combine list are no longer sent to SAP. Assume this is because by the time Table.Combine starts processing them, they are already somehow cached.

Re: Table.Combine in parallel possible?

BartVW — Mon, 16 Sep 2024 09:36:12 GMT

Would you have any idea lbendlin ?

Re: Table.Combine in parallel possible?

lbendlin — Mon, 16 Sep 2024 10:37:04 GMT

In such scenarios we create separate queries, disable load for all, and return a query combine rather than a table combine. Make sure not to overwhelm the data source when you crank the parallelism up to 30.

Re: Table.Combine in parallel possible?

BartVW — Mon, 16 Sep 2024 10:57:31 GMT

Thanks lbendlin . Could share the M function that is used for a query combine?

Re: Table.Combine in parallel possible?

lbendlin — Mon, 16 Sep 2024 11:19:50 GMT

Query1 & Query2 & Query3 ...

If the queries are not too large you can also do it DAX UNION instead.

Re: Table.Combine in parallel possible?

BartVW — Mon, 16 Sep 2024 11:51:47 GMT

I tried this (disabling load and query combine), but in this way it still executes the sub queries one after the other, not in parallel.

Re: Table.Combine in parallel possible?

lbendlin — Mon, 16 Sep 2024 12:00:28 GMT

Then go for the DAX UNION. You can hide the tables in Power BI