cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdamW
Helper III
Helper III

Can't List Rows in Excel when table contains SQL data merged with SharePoint data?

Can anyone explain this error? This is what I am getting when I try to use the List Rows Present in a Table for Excel action. 

 

I have an Excel file that contains a power query where I am mixing together SQL data with a SharePoint list.  Why is this not allowed?

 

 

The dynamic invocation request failed with error: Unexpected error occurred when calling the ApiHubsRuntime API: 'Microsoft.Azure.ProcessSimple.Data.Entities.Exceptions.ProcessSimpleDataException: The ApiHubsRuntime API call failed with http status code 'BadGateway' and response content '{ "error": { "code": 502, "source": "unitedstates-002.azure-apim.net", "clientRequestId": "d5be53f2-3f6b-453f-a323-xxxx-xxxxxxx", "message": "BadGateway", "innerError": { "status": 502, "message": "This workbook cannot be edited because it contains the following features:\r\n\r\n• SharePoint lists\r\n\r\nTo edit this workbook, open the workbook for viewing, select Save a Copy from the File tab, and edit the new copy.\r\nclientRequestId: d5be53f2-3f6b-453f-a323-xxxx-xxxxxxx", "error": { "message": "This workbook cannot be edited because it contains the following features:\r\n\r\n• SharePoint lists\r\n\r\nTo edit this workbook, open the workbook for viewing, select Save a Copy from the File tab, and edit the new copy." }, "source": "excelonline-ncus.azconn-ncus.p.azurewebsites.net" } } }' at Microsoft.Azure.ProcessSimple.Data.DataProviders.AzureResourceManagerDataProvider.<>c__DisplayClass52_0`2.<<CallAzureResourceManager>b__0>d.MoveNext() in X:\bt\1220470\repo\src\processsimple\Roles\ProcessSimple.Data\DataProviders\Services\AzureResourceManagerDataProvider.cs:line 587 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.WindowsAzure.ResourceStack.Common.Algorithms.AsyncRetry.<Retry>d__3`1.MoveNext() in X:\bt\1207438\repo\src\common\core\algorithms\AsyncRetry.cs:line 0 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.WindowsAzure.ResourceStack.Common.Algorithms.AsyncRetry.<Retry>d__3`1.MoveNext() in X:\bt\1207438\repo\src\common\core\algorithms\AsyncRetry.cs:line 171 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.ProcessSimple.Data.DataProviders.AzureResourceManagerDataProvider.<CallAzureResourceManager>d__52`2.MoveNext() in X:\bt\1220470\repo\src\processsimple\Roles\ProcessSimple.Data\DataProviders\Services\AzureResourceManagerDataProvider.cs:line 0'.

3 REPLIES 3
eliotcole
Power Participant
Power Participant

I'm not sure, but it looks like somewhere in the process it thinks you're trying to edit soemthing that you should be doing via another method. (editing the SP data)

 

Purely out of curiousity ... Is there any reason why excel needs to be involved at all in this situation? It seems that if you're handling SQL and SharePoint data you might be better served doing whatever you need either in Power Automate or in PA with some of the AI/Dataverse stuff.

 

Is this just a legacy thing?

 

If so, can the same data be arranged using one of the methods that don't involve excel, then you just update/create/replace a static excel sheet somewhere for whomever needs the data in excel?

AdamW
Helper III
Helper III

The reason Excel is involved is because I got the same error in Power Query online when trying to merge this SQL table with SharePoint.  It just won't allow it.

 

My workaround has been to use Excel and then link a different Excel workbook to the one with the query and update it that way.  It's a tedious workaround and I need to find a different way.

 

I'm not editing any SharePoint data.  I am only merging it to some SQL data.

eliotcole
Power Participant
Power Participant

So (sorry, not to belabour the point), could you not create a copy of the SharePoint list, then use a Power Automate SQL connector to pull in the SQL data and merge both into the new SP list?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,332)