Data Flow refresh fails with no error indication

[ToddChitt]

I'm a little new to Data flows. Seems straight forward enough. I have created one that pulls 3 tables from Excel, through a personal gateway. I can get in to edit it and refresh each query individually. But when I close, it asks if I want to refresh. I click yes. It spins for a couple minutes then reports an error, but gives no details. 

Why would it fail if each individual query ran fine?

FYI, I'm only doing this because of the horrible functional delingquincy around  the fact that the service does NOT honor the power query setting of "Include in Report Refresh" being disabled.

[v-lid-msft]

Hi @ToddChitt ,

Have you put some function such as merge or append with theree tables? Does the three tables come from the same excel? Could you please try to use the same query in the Power BI desktop  to verify if the query can work and get data?

Best regards,

[ToddChitt]

I started to respond yesterday but then some big red banner apeared on my browser window about lost credentials or some such annoyance and I lost everything.

Moving on...

The Dataflow had three datasets tied to Excel tabs on a local file, refreshed through a personal gateway. But when I went to set up Scheduled Refresh, I saw like 6 or 8 queries that needed to be satisfied, most of them referencing SharePoint online documents that *may* have been left overs from previous attempts to get the Excel data out of SharePoint. Even though I deleted the Entities in the Dataflow, it must have hung on to the queries.

Seeing a it was not far into the development, I simply toasted it and started fresh, this time with local CSV files.

Someone can try this, though: Copy the link for a file stored in SharePoint online and try to use it as the URL for an Excel connection. I don't think I ever got it to work, even after researching the issue and finding that the link contains extra characters /r:/s or some such nonesense. I bet those are hold-overs from those attempts.

If so, it's a BUG that the Dataflow holds onto and want to refresh links for entities that have been deleted.

[ToddChitt]

I can classify this as a BUG. Here are my steps to reproduce:

1) Start a new dataflow, add an Entity, select Excel as the type

2) Go to a SharePoint online site hosted by your company, one of which uses your organizatonal credentials to open. 

3) browse down to an Excel workbook that has data. Click the elipsis for the file and select Copy Link

4) wait for it to create the link in the popup, then click Copy.

5) Open Notepad and paste in the link. You will see a character set between the "https://<mycompany>.sharepoint.com/" and the site name, character like "/:x:/s" Remove these from the link, copy the rest.

6) back in the Dataflow, paste in that link for the Excel URL.

7) Click Next. I kept getting an error of "Invalid Credentials" which doens't make sense.

😎 Copy the link for a LOCAL Excel file, including file name, and paste that into the File Path or URL box, overwriting what is there.

9) Click Next, go through and configure the entitiy, specify a personal gateway, credentials, etc. and finish creating the entitiy.

10) Click the button for Scheduled Refresh for this Dataflow. I see the following:

BUG: It retained the SharePoint URL for the one query that was eventually abandoned. Even though I have only ONE entity in the dataflow, there are TWO connections that must be satisfied in order for a refresh of any kind to go through.

[v-lid-msft]

Hi @ToddChitt ,

Thank you for your reproduce steps, we have the same result as yours, we will report this issue and update here if we got anything new. We apologize for any inconvenience this issue have caused.

Best regards,

[GilbertQ]

Hi there

What happens if you enable verbose logging on your Personal Gateway?

There could be an error there?