cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Applying Queries stuck with Creating Connection Model

Hello! 

 

I changed my main data query today and significantly reduced the fields that are downloaded from WEB API source. Most of the fields are not needed so I decided instead of loading all and then deleting the unused ones, that I'll pull only the needed ones. Now after those changes in Query Editor preview it looks all good and is snappy fast. But when I try Close & Apply it gets stuck in Creating Connection in Model. Even after saving and closing Power BI Desktop and trying to apply, same issues. I left it running for hours. No change. What can I do to force it through? 

 

Got Version: 2.62.5222.601 64-bit (September 2018) so has nothing to do with Jan 2018 update. Query had 3384 columns and 622 rows before. Now 16 columns and 624 rows. So should be done in 5-10 secs. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Applying Queries stuck with Creating Connection Model

Hello Lydia, 

 

Yes, I did delete these columns and rows in Query Editor of Power BI Desktop after importing all the data. Normally everything takes around 20-30 seconds to pull the data, build the model and connections, finish all the calculations and visualise it. I do know it takes a performant machine and use an 8 processor 32GB RAM machine for it. 

Since you mention it, I am reducing the columns in my query and try to promote the changes from Query Editor. But that is exactly causing the issue. That's what I did to improve the performance. I let it run for several hours with no success. So now I had to get drastic and go to manage relationships, delete the concerned query there, then apply and reconnect all relationships. That solved the problem and that's what I would've expected the Apply&Close to do as well. I do not have access to the API to modify the columns, but I modified the query to only pull the fields I want by including "?fields=..." in the request URL and it works like a charm. 

 

Regards,

Jens

 

 

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: Applying Queries stuck with Creating Connection Model

@jsteinigen,

Do you delete these columns and rows in Query Editor of Power BI Desktop after importing all the data? If so, this operation and other transformations( merge/appen queries, etc.) will consume memory and cause the performance issue as you describe.

Is there any possibility that you limit the columns and rows in the web API url when you import data to Power BI?

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.
Highlighted
Frequent Visitor

Re: Applying Queries stuck with Creating Connection Model

Hello Lydia, 

 

Yes, I did delete these columns and rows in Query Editor of Power BI Desktop after importing all the data. Normally everything takes around 20-30 seconds to pull the data, build the model and connections, finish all the calculations and visualise it. I do know it takes a performant machine and use an 8 processor 32GB RAM machine for it. 

Since you mention it, I am reducing the columns in my query and try to promote the changes from Query Editor. But that is exactly causing the issue. That's what I did to improve the performance. I let it run for several hours with no success. So now I had to get drastic and go to manage relationships, delete the concerned query there, then apply and reconnect all relationships. That solved the problem and that's what I would've expected the Apply&Close to do as well. I do not have access to the API to modify the columns, but I modified the query to only pull the fields I want by including "?fields=..." in the request URL and it works like a charm. 

 

Regards,

Jens

 

 

View solution in original post

Highlighted
Frequent Visitor

Re: Applying Queries stuck with Creating Connection Model

@jsteinigen I had a similar problem. I"m using the January 2020 version of Power BI Desktop Optimized for Report Server. I created a custom function and used it to create a custom column in my query. It ran fine in the query editor but when I try "Close and apply" it always crashes in the "Creating connections in model" step.

 

Based on your post, I went into the Data Modeling view and deleted the table from the data model. Then I could do "Close and apply" from the Query editor without an error. But the data was not part of the data model any more. I thought I would try to add it back. The only way I could find to do that was to go in to Query Editor again. Right-click the query and set the properties to load the table. Then when I tried to refresh the report, again it died in "Creating connections in model"

 

Were you able to add the problematic table back to the model? If so, how did yoiu do it?

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors