Stuck on "Creating connection in model..." after unpivoting columns

[Loubot3000]

After unpivoting columns in the query editor, power bi is getting stuck on "Creating connection in model..."

The data is coming form an excel file. It's fairly hefty - ~15000 rows or so and some of the sheets have thousands of columns columns.

I can confirm that prior to unpivoting, the data is coming through completely fine. I have also made sure to delete blank rows and columns (as far as I can tell).

I have deleted all relationships and turned off the auto-detect relationship settings.

Any ideas what's happening?

[Whyneedname]

Share my resolution here that click edit query on your sheet and scroll up and down or left to right to see if any unnamed columns or rows (in my case over 1000+ null columns) in your table. 

After then removed the unnecessary columns or rows and apply, the report refreshed very quickly

[juan_pablo]

Just in case: I solved this problem when I found out that a excel file was importing all possible blank columns of the file (until column XFD). So I included a "Remove Other Columns" step in my query and the problem was solved.  

[as-akash]

My problem got resolved when I found that one of my data source was having external connections (that was created using power query inside that data source) and I removed the external data connection in the respective data source.

Instead of refreshing all connections at once, try refreshing one by one and check which connection is getting stuck and troubleshoot.

[v-yangliu-msft · Accepted solution]

Hi  @Loubot3000 ,

In the Power BI desktop, to improve performance when connecting, turn off the following options and settings - > options in Data Load: "Time Intelligence", "Background Data".

Also related to the large amount of data, you can try turning off the "Include in report refresh" option for tables that will not be calculated.

If your Power BI Desktop is up to date, if not, update at the following link:

Download Microsoft Power BI Desktop from Official Microsoft Download Center

This is the related document, you can view this content:

stuck in creating connection in model - Microsoft Power BI Community

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.