Table.combine - Append process takes too long. What to do?

[Gabry]

Hello guys,

I have one table with 100 million rows and another with about 250K rows. I'm using one dataflow to retrieve this data from a SQL Server, and then a second dataflow to append them. The problem is that the append operation takes a very long time—around 2 hours. How is that possible for such a simple append? I'm using the Table.Combine function in M. Both tables have the same columns, and I've enabled the Enhanced Compute Engine. What can I do to optimize this process? I'm using PPU

Avoiding setting incremental refresh on the computed table.

Thanks for any suggestion.

PS. My code is just:

Source = Table.combine(table1, table2)

[lbendlin]

Do the tables have the same columns?  In the same order?  How many columns?

It takes that long because your operation breaks query folding.  Both tables have to be ingested completely before they can be appended.

What's wrong with incremental refresh?

[Gabry]

Hi @lbendlin ,

thanks for your reply.

Yes they have the same number of columns, also same names. There are 37 columns. Yes same order of the columns (why does this really matter?).

Which operation breaks query folding? Append? Keep in mind that this transformation does not query the SQL source but operates on linked tables. generating a computed table. So does query folding still matters? I checked the dataflow's logs and under Compute engine it says cached and reduced.

I do incremental refresh on the first dataflow that gets data from the SQL source but I would prefer to avoid incremental refresh on this computed entity because I need to do some transformation on it. As a last resort, I could implement it, but I would like to understand why it takes so long for a simple append and if there is a way to optimize it.

[lbendlin]

The Dataflow Power Query Editor should indicate the point where the folding breaks and why.

Can you append them via "&" ?  (shouldn't really make a difference but worth a try)

What if you change the append order, using the smaller table first?  

Use a Table.Buffer on the smaller table.

[Gabry]

I tried using Table.Buffer, but it didn't work. When I saved this modification, after some processing time, it said it couldn't validate the query and couldn't analyze the problem. Tried 4 times. (Maybe the tabe is too big to buffer? I don't know)

table2buffer= Table.Buffer(table2),
    Source = Table.Combine({table2buffer, table1}),

Query folding breaks at first step (Source=table.combine(tab1,tab2):

There aren't any explanation on why it happens.

I've tried to change the order of the append and nothing changed. Also tried using &, nothing changed 🙁

[lbendlin]

Since you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi

[Gabry]

I've a Premium License. Can I open a ticket to ask for optimization support? Really?

[lbendlin]

There is no such thing as a Premium license.  You have a Premium Capacity, and a Pro license.

You can reasonably expect the append to work. It doesn't work, so you consider it a bug. Raise a ticket for that perceived bug.

[Gabry]

Sorry, I don't understand what do you mean. I've Premium Per user Licensing https://learn.microsoft.com/en-gb/power-bi/enterprise/service-premium-per-user-faq

Ok, I'll try to raise a ticket, thanks for the help 😉

[lbendlin]

Yes, sorry, I always forget these exist. My bad.