cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft Michaela_Onu
Microsoft

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

@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.
Capture.PNG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Moderator v-yuezhe-msft
Moderator

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

@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.
Capture.PNG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors