Is there a row limit for Append queries?

[DineshM]

I have a table with 1.5 million rows. When I tried connecting to that table in Direct Query, Power BI applies a limit of 1 million rows.

So I created another table with the same set of columns and split the data between the two tables by applying filters.

Now 1 table has 1 million rows, and the other has 500k rows. Now can I use the "Append query as new" option within Power BI and create a single table with 1.5 million rows for my analysis?

ps: I couldn't switch to Import mode as the data updates happen too frequently (that scheduled refresh is ruled out) and the user wants to see the latest data when they view the report.

[jaweher899]

No, the "Append Query as New" option in Power BI is not applicable for Direct Query data sources. Direct Query is designed to retrieve data from the source database in real-time, without caching the data in Power BI. As a result, any operations that modify the data, such as appending tables, cannot be performed within Power BI. You may consider using other data integration or data warehousing techniques to combine the two tables into a single, larger table before connecting to Power BI in Direct Query mode.

[DineshM]

Hi Jaweher899

Thank you for the reply. However, I tried this for a relatively smaller dataset, one table with 1000 rows, and the other with another 1000 rows.

I connected to those tables (with same columns) in Direct Query, and then used the "Append query as new" option in Power BI. The appended table shows up with 2000 rows. When I hit refresh, both the 2 source tables refreshes and the third appended table also works as desired.

When I tried this with the real dataset of 1.5 million rows, each source table takes about 15 minutes to load (which is probably due to the performance limitations of the data source). And when I tried appending, Power BI is neither showing an error nor showing the results of the appended table.

So the question boils down to the number of rows in the appended table? Can it have more than 1 million rows when using Direct Query for the source tables?