Query Load Time of Appended Table vs Individual Tables

[PowerBI-Guy]

I'm trying to figure out the best option to load my data into Power BI.

I have 4 tables, identical in structure, and each has about 1.5M rows.

While testing, I initially brought each of the 4 tables into Power Query, then loaded the 4 tables seperately into Power BI.

Refresh took approximately 15mins for each table, but running simultaneously so about 15mins total also.

However, I want to append these into 1 table. The problem I'm having is occurring when appending in Power Query (using Table.Combine()). I disable the load on the individual tables and load the appended table.

Now my appended table is taking approximately 15mins for each source table, but is not loading them simultaneously, so the new load time is approximately 60mins (4tables x 15mins).

The alternative option is to pull the 4 tables into Power BI and use UNION(), but I would prefer to do the transformations in Power Query for report performance. Is there any way I can "set" the append to load the data simultaneously?

My data source doesn't allow me to do the transforms using an alternative method to Power Query so has to be done there.

Appreciate any advice, thanks!

[m_dekorte]

Hi @PowerBI-Guy,

If all files have the same structure and need the same transformations applied to them, examine the from Folder connector, instead. Next perform all transformations on nested tables before the Expand and Combine operation.

If you're unfamiliar here's a Step by Step Guide to combine tables from files in a folder. Not exactly addressing your requirement but it does illustrate the key parts.

Hope that helps.

[PowerBI-Guy]

My data source is through SAP business warehouse and, as set up by the organisation, I am unable to make any transforms before the data hits Power Query unfortunately.

[ppm1]

Your plan to append the tables is the right way to go. Please make sure your query is folding back to the source (if you have any filter/select/group by steps). Also, are your data date based? If so, are your leveraging incremental refresh?

Pat