Appending Queries Issues

[nirvana_moksh]

Hello All

This has been an ongoing issue and I have tried everything but now I almost feel as this is an issue with PBI. So I am connecting to a total of 20 SQL Views which on SSMS side run in total under 1 minute - 1 minute 28 secs tops and returns a mere 900 row result. I dont do any crazy transformations on eah of these queries and only basically add 3 custom columns in each and when I dont apppend it runs very very fast, as soon as I append all 20 into a new query things go crazy and it takes 30-35 minutes to do the whole refresh.

Has anyone experienced this? I find this very very strange especially given that I am not doing anything crazy and the data set is nothing at all.

[Anonymous]

@nirvana_moksh,

For all the tables except the append query, disable "Enable Load" option in query editor, this way, apply only the append query to report view of Power BI Desktop. When data changes in your source tables, click "Refresh" in Report View of Power BI Desktop to bring new data to append query.


If append query still takes long time, create the append table  with DAX instead by using UNION() function. Or you can create the append table in SQL Server and then import it to Power BI Desktop.

Regards,

Lydia Zhang

[nirvana_moksh]

Also, the UNION in DAX wont work as the UNION in M would work because each view has a different layout of columns.

[nirvana_moksh]

I did already disable the load feature for the base tables and I do not want create this global view in my PROD SQL environment only to keep that cleaner and I wanted to do this at the report level, but regardless should this not be an easy and fast load for like a mere 1000 rows? That is what surprises me, I know if I create this appended UNION in SQL it will be quicker but why does it take so much longer in Power BI?

[Anonymous]

@nirvana_moksh,

Do you Refresh data in Report view or Query Editor?

In addition, even if you have different layout columns in these tables, you can use UNION() function in DAX, there is a similar thread for your reference.


Regards,
Lydia

[nirvana_moksh]

When I refresh data in the query editor using Refresh All it takes maybe 8-12 minutes, but when I hit Close and Apply, the refresh in the report view takes 29 minutes.

When I tried using the UNION DAX function with the 19 views it gave me the error of columns number mismatch.

[Anonymous]

@nirvana_moksh,

Have you used SELECTCOLUMNS() function to choose same number of columns in each table?  There is a similar thread for your reference.
https://community.powerbi.com/t5/Desktop/Union-thinks-column-numbers-are-different/td-p/174218

Regards,
Lydia