topic Cannot Publish Dataflow (sourcing data from Excel SharePoint) in Dataflow

Cannot Publish Dataflow (sourcing data from Excel SharePoint)

st_0999 — Wed, 02 Aug 2023 10:18:50 GMT

Hi,

I've tried several times to publish a dataflow, but I keep getting the following error.

It sources from a SharePoint folder, and transforms a few tables from a few excel files.

It's not overly complex, but it's not super simple either.

I set the data destination of a few tables to a Lakehouse I created, and map all the column names and data types successfully.

But when I press the publish button, it tries to publish it. I have to wait for about 4-5 minutes, and then this dark orange triangle appears with the following messsage.

I've tried several times, to re-publish, but same error.

I get no other feedback.

It this a license issue? I'd be surprised if it is, given the sizes of data involved.

Please do help

Re: Cannot Publish Dataflow (sourcing data from Excel SharePoint)

miguel — Wed, 02 Aug 2023 16:37:27 GMT

Hey!

Would you mind helping us by answering some questions to better understand your situation?

Does this solution work in Dataflows Gen1 in Power BI?
What's the file extension of the files that you're trying to combine?
What happens if you simplify your transform query and your queries overall to the most simplistic form? does it publish and run or does it also give you an error?
Are you able to share some repro steps on how to reach this error? I've tried running this on my end but can't seem to be getting any isues however

Re: Cannot Publish Dataflow (sourcing data from Excel SharePoint)

st_0999 — Wed, 02 Aug 2023 16:46:23 GMT

Several issues whilst trying to solve with MS Dataflow Gen2 will fail if - Last step in query has a Binary column - Some custom functions will work, some will not. I had a fnGetParameter custom function that kept failing - If the query name itself is too long, it causes some other string to spill over 128 characters, that will cause another error - Any lists or numbers in the query chain (where the final step of the query is in this form), will cause the whole Dataflow to fail We went through the above, shortened the query names, and it did publish! But then, it failed to refresh (since one of the tables, it gives this one the 2nd to last table in the query chain: Error Code: Mashup Exception Data Source Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataSource.Error: Failed to insert a table. Details: [DataSourceKind = "Lakehouse", DataSourcePath = "Lakehouse", Message = "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)", ErrorCode = -2146232060, Number = 10054, Class = 20] (Request ID: *************************************). The entire query chain takes 3-4 minutes in Excel. I thought Gen2 would zip through this in no time, but apparently not. I still can't get it to refresh reliably at all (not even once yet)

Re: Cannot Publish Dataflow (sourcing data from Excel SharePoint)

st_0999 — Wed, 02 Aug 2023 16:48:01 GMT

Several issues whilst trying to solve with MS Dataflow Gen2 will fail if - Last step in query has a Binary column - Some custom functions will work, some will not. I had a fnGetParameter custom function that kept failing - If the query name itself is too long, it causes some other string to spill over 128 characters, that will cause another error - Any lists or numbers in the query chain (where the final step of the query) is in this form, will cause the whole Dataflow to fail We went through the above, shortened the query names, and it did publish! But then, it failed to refresh (since one of the tables, it gives this one the 2nd to last table in the query chain: Error Code: Mashup Exception Data Source Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataSource.Error: Failed to insert a table. Details: [DataSourceKind = "Lakehouse", DataSourcePath = "Lakehouse", Message = "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)", ErrorCode = -2146232060, Number = 10054, Class = 20] (Request ID: *************************************). The entire query chain takes 3-4 minutes in Excel. I thought Gen2 would zip through this in no time, but apparently not. I still can't get it to refresh reliably at all (not even once yet)

Re: Cannot Publish Dataflow (sourcing data from Excel SharePoint)

miguel — Wed, 02 Aug 2023 16:51:30 GMT

You could take a look at the limitations that we have today in Dataflow Gen2:

Data Factory Dataflow Gen2 limitations - Microsoft Fabric | Microsoft Learn

For the issue that you're reporting, it commonly only appears if you're using a Gateway. Are you currently using a gateway for that dataflow? if yes, please let me know if the article below works for you:

On-premises data gateway considerations for data destinations in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

Re: Cannot Publish Dataflow (sourcing data from Excel SharePoint)

st_0999 — Wed, 02 Aug 2023 16:59:31 GMT

How can I tell if I'm using gateway? All I can see for the dataflow settings is this: https://pasteboard.co/OriB64BUsFRg.png Do I need a better license?

Re: Cannot Publish Dataflow (sourcing data from Excel SharePoint)

miguel — Wed, 02 Aug 2023 17:02:37 GMT

You can check the article below to get more information on how to check the gateway used for your dataflow (if any):

Change the gateway used in a dataflow project - Power Query | Microsoft Learn

Re: Cannot Publish Dataflow (sourcing data from Excel SharePoint)

st_0999 — Wed, 02 Aug 2023 17:06:19 GMT

Thanks. I clicked options and it says Gateway - none So there is no gateway it appears. It's a fairly simple query, that sources from a Sharepoint folder, form a table in an excel file. When I run this in Excel itself it takes about 5-6 minutes. Just a little surprised it is "forcibly closing the connection from the remote host" I've tried refreshing about 5 times... so am just giving up until MS get back to me.

Re: Cannot Publish Dataflow (sourcing data from Excel SharePoint)

miguel — Wed, 02 Aug 2023 17:10:56 GMT

For Excel files with the .xls or .xlsb extension, you need a gateway to make things work (as there's a dependency on a driver that needs to run in the gateway to read those files).

If your files do not have any of these extensions, then do please reach out to Microsoft's Support Team by raising a support ticket. Below is the link where you can raise a support ticket to engage directly with the engineering team:

https://support.fabric.microsoft.com/support

Re: Cannot Publish Dataflow (sourcing data from Excel SharePoint)

st_0999 — Wed, 02 Aug 2023 17:13:48 GMT

Thanks. It is a .xlsx file, so that's not an issue either. In power query online, it loads the final table in the query chain in about 10 seconds.. I see the first 100 rows. But the refresh, after I publish it, takes about 13 minutes, then it gives that error message. I've raised a ticket with MS.