Changing PowerPivot source in a Power BI (+ query limitation)

[Michaela_Onu]

Hello, 

I have been building quite a complicated model in Power BI; as it uses more databases at once, I first created an underlying model in PowerPivot, created all connections and then imported the contents into Power BI. I have now finished with the whole report, but found out my largest query (2+ million rows) cannot get refreshed. In PowerPivot, I have no trouble refreshing, but Power BI just doesn't want to process it - the Evaluating part of refresh takes forever and then it says that "Error was encountered in the transport layer". In Edit queries, I get the following error code:  ErrorCode=-2147467259.

I have different queries coming from the same data source, one even with the same kind of data, just for a different period of time, and I have no trouble with the refresh, so I suppose it is the magnitude of the data I am pulling in that is disabling the refresh. I even tried a fresh new Power BI file, imported all the contents fine but again, couldn't refresh the query. I would like to use a workaround, split the query into some parts and reupload it, but I am not sure if it even works (it will probably delete my PBI contents and start a blank file). Is there a way to use this workaround and, at the same time, not lose all the visuals and contents that took me 3 days to finalise? 

Thank you, 
Michaela

[v-yuezhe-msft · Accepted solution]

@Michaela_Onu,

After spliting your queries in PowerPivot and upload these queries into Power BI Desktop, you can use UNION() function to combine them and then disable "enable load " option for these splited queries in Power  BI Desktop query editor.


In addition, to make everything smooth without losing visuals, please make sure the combined table have same name and same fields with the original large query.


Regards,
Lydia