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

[AdamW]

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'.

[eliotcole]

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]

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]

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?