Unable to publish report to Power BI service (SharePoint Online heavy data source)

[dancarr]

I'm having repeated issues trying to publish my report to the Power BI service from Desktop. There is no error, it just constantly ticks over, the first time I left it 24 - still nothing (and no network traffic in Task Manager also).

I have no other issues publishing data to the online workspace, and my colleagues (also with full admin rights) get the same problem. 

The report uses 8 SharePoint Online lists (all on the same site), that do include the 'person/group', 'choice' and 'lookup' data types. It also includes a query to a MS Forms input (which adds to/updates a Excel online file in our Power BI publishing space) and a query to an Excel sheet on a different SharePoint Folder to the Online lists. I have access rights to all these sources with organisational sign on.

I have attempted

- Removing the MS Forms query

- Removing the Excel query (different site from the list)

- Optimising reducing the SharePoint Online query steps (by removing all other columns other than 'Fieldsastext' and expanding that column only)

- Asking colleagues to attempt publishing 

- Saved as different filenames

- Tried to publish to a different Online Workspace

- Setting up a completely fresh file, and manually copying over the query

4 online lists are around 150 rows with <6 columns, 3 are <50 rows and < 5 columns, and 1 around 1000 rows, with around 10 columns (most are lookups or choices). The latter is around 4mb.

Until optimising the query the queries there was quite a large refresh time, now it is very quick. The data model is around 10 tables, with around 2/3 being bi-directional - there are no many-many relationships.

I have other functioning reports that do access some of the 150 row SharePoint online lists, which makes me think the problem could be the larger list.

I'm unable to publish. What could this be, and is there a work around?

[parry2k]

@dancarr well your data source and power query shouldn't matter when you are publishing the report, not sure what is going on, two reasons, either there is an outage in your area (power bi service) or your internet/network issue. You can check at this https://powerbi.microsoft.com/en-us/support/ page to see if there is an issue with power bi service in your region.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[dancarr]

Hi, thanks for your reply @parry2k .

No status issues here in Europe, and we have 23Mbps/9Mbps (DL/UL) internet speed with no other upload issues.

There is one awareness warning for SharePoint online 'Power BI customers with Premium Capacities using SharePoint Online as a data source may see refresh not complete successfully with the following message - "The underlying connection was closed: An unexpected error occurred on a send". Until the issue is resolved in Premium capacity, a temporary workaround would be to move the workspace where the dataset is running to shared capacity and an on-demand\schedule refresh can be configured. SharePoint engineers have identified a fix and have started a rollout of the fix to all environments, expected date to be deployed to all environments is by end-of-day 06/09/2020.'

But I get no issues obtaining data, refreshing data, using it on desktop, its only the inability to publish.

I set it on again (now with quick refresh times <30sec) overnight (12+ hours) and its still spinning (note, its not like the spinner is completely frozen, it peridically spins around - also no network traffic in task manager but the application is using memory)

[parry2k]

@dancarr not sure what else to tell. maybe try to connect to a different internet, like use phone data for internet and test that works, or try different machine who knows it is related to the machine, otherwise open a support ticket.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[dancarr]

I've tried the different internet and machine, unfortunately the same thing.

How would I open a support ticket?

[dancarr · Accepted solution]

The problem was solved.

The solution was not what I was expecting at all, but arising from a corrupted JSON file used to fetch and load our company's colour pallette which was imported as a theme. This made the URL within it effectively never ending when called.

Possibly the last thing ever considered, but maybe this might help others with similar issues in the future.