stucking on creating connection in model

[Leann]

Hi,

I have a data model in power bi desktop, the tables are loaded from ssas server, and it refreshed data well yesterday.

But today when I pulled in more data (400,000 rows to 17,986,280 rows) to ssas server, and click refresh data of the fact table (refresh only one table), it's stucking in "Creating connection in model" 1 hour...(still stucking)

Also, I've exmained the table which has no blank columns. is it normal? 

or any experience to check the problem efficiently?

Thanks a lot!

[JE-WeVite]

What worked for me is:

1. Manually refresh tables in the "Data" pane until I found which table was not able to create a connection and was stuck
   • Right click, Refresh Data on each
2. Delete that table from the model
3. Add that table back by directly connecting to it (again) via "New Source" (and in my case "Dataflows")
4. Refresh that table manually as a test (worked!)
5. Refresh all tables at once (worked!)

NOTE: In my case, this was a small table (57 rows) from a Dataflows connection to the workspace, and I was able to view the data in PowerQuery preview.

What DID NOT work:

1. Changing settings in the File or Global options related to Data Load
   • Evaluations, memory
   • Set parallel loading of tables to one, and unchecking import/update/autodetect for relationships
   • Turning off time intelligence and background data loading
2. Clearing the cache
3. Deleting all relationships to the bad table
4. Deleting the table and adding it back in by copy/paste of PowerQuery code in Advanced Editor into a blank query did not fix the problem
5. Downgrading to a previous version

[marlark]

As of 1/30/24 I started experiencing this issue with none of the proposed solutions being successful. Are there any new updates on this root cause?

[mwhalen]

I've been driving myself crazy for the last 3 days trying to figure this issue out. The only message I have gotten pertained to my model not being able to refresh due to memory, so I tried unselecting the boxes at data load, I've removed like 60% of the columns across all my tables, and still can't refresh, data load, or publish anything. I can't believe there hasn't been a post or solution from this week regarding this issue!

[marlark]

I identified my issue. There was an Append with two JDE tables and an Excel based file. Someone saved the file with the max number of Columns an Excel Sheet can handle. This caused a cascade effect where the root cause of off my radar because the owner of the file thought they built adequate restrictions on the file. *looks at the new hire*

[ChristopheFromS]

Since a couple of weeks, I can't open some report because of memory issue ("Out Of Memory").

They probably have reduced their capacity. 😐

[marlark]

Take a look at my reply to mwhalen. It may be basic unexpected scenario like mine. *again, looks at the new hire*

If you have not looked yet, PowerBI desktop has a memory cache. Your issue may be tied to that if you have not checked already. When I thought that was my issue I had >2GB of cache. As noted in the other reply, it didn't solve my issue when I cleared it.

[mwhalen]

Not sure what my issue was. It seems to be fine now, either Microsoft fixed something, or it really required me letting a refresh in the Desktop app run for like 8 hours, then hours for publishing. I don't think it has anything to do with Sharepoint or number of columns in Excel, since everything in my semantic model is coming from SQL.

[vijayk416]

Exactly the same here. I am thinking a global change has happened with Sharepoint.

[Vipuls1011]

I was loading data from flat files when i got this error. It somehow created multiple columns in my data sheet and due to which I was stuck at creating connection in model. I just deleted those unnecessory columns (though it showed blank in my original excel file), and problem is resolved. 

[DiegoAuvert]

Trying to load a recurrent csv input, and this time is not working. Stuck in Load " Creating connection in the model." I normally have to modify the query i.e, if any header changes or new columns to incorporate w/o problems in the past. This time, new columns were modified in the power query to make the load pass the evaluation but stuck in the creating connection

[benstrom]

Hi,

just want to add another possible cause for this issue as it has happened to me a couple of times now. If you import data to PBI from an excel sheet make sure to delete any empty columns on the right of the table you're loading (just mark the columns and press delete). It seems like PBI is trying to create connections to all the empty columns as well and then it takes forever to refresh.

//Benny

[dmaddern]

Hello,

I had the same issue with a new table i added to an existing data set. I removed the new table, then readded it and it worked for me.

Maybe that will help for you

[FSmyth]

Hi I believe it crashes as it's trying to pull endless columns so you need to save the information you have as a table so it just read and puts the data other with the upgrade it's been defaulted to pull endless columns

[Taz_Car]

Hi, does anyone have any other suggestions.  I am connecting to excel files in sharepoint and am getting blocked at the "Creating connections in model" stage.  I have tried everythgin above, but no joy.

[orangeatom]

hi @bharath47 in August 2022, this issue is back, advise on solutions. Latest PowerBI client is downloaded as of August 4th, 2022.

[saintsitive]

September 2022 still doesn't fix the issue for me, still stuck on creating connection in model for bi that import data from web 😞

[bharath47]

@orangeatom - Workaround could be to manually refresh the table one by one in Power BI Desktop "InsteadOf" RefreshAll. That helped me progress.

[bharath47]

Hi Team, started having the same issue since this week's Aug 2022 update. Any resolution? 

[inesnovo7]

Same here! Don't know what to do. 😞

[bharath47]

@inesnovo7 try manual refresh of individual tables once and then try RefreshAll. Not incurring this issue after this step.