Unanswered

Flow breaks Excel connections to external sources

(1) Share

Report

Posted on by Community Power Pla...

I have two Excel files (call them A and B), both have tables. Both reside on OneDrive for Buisness.

File A has a table with external source (another file on OneDrive). On recurrence, Flow takes one row from that table in file A and adds it to a table in file B.

Very, very nice.

However, I noticed that anytime Flow does this (Get rows, insert rows) ALL Excel connections are broken (picture below).

I kind of understand why - it adds a column (__PowerApssId__) to the table in file B making the table different to its source. That is annoying but ok.

What is NOT OK is that my connections in File A are broken as well.

It seems that anytime Flow gets a row from a table, external sources are disconnected, making it impossible for me to refresh the

data without estaablishing the connection again.

Any ideas why?

Categories:

Connecting to Data

All responses (10)

Answers (0)

Fiturbe 6 on at

Like (0)

Report

Re: Flow breaks Excel connections to external sources

Is there any new info about this issue? I have exactly the same problem. After Flow touches my Excel file all data connections are unlinked from tables in the file, even if those tables are not used by the Excel connector and are even in separate sheets.

This is a real problem, as it makes it impossible to connect a flow to an Excel file that depends on other sources of data.
Community Power Pla... on at

Like (2)

Report

Re: Flow breaks Excel connections to external sources

Thanks @Anonymous

That seems like a simple, efficient solution (in other words, the best kind ). I had started down a complicated path of trying to move information two ways (to the connected source for powerappsids and from the connected source for everything else) that was guaranteed to cause headaches later, so I appreciate you replying so quickly and saving me from myself.

EDIT: I should add, in case others are in the same situation, that if the excel file is published to Power BI, that service can auto-refresh connected sources (either stored in Onedrive or through a local gateway) so such a script can just focus on copying, if applicable.
Community Power Pla... on at

Like (1)

Report

Re: Flow breaks Excel connections to external sources

Hi @Anonymous
Not sure it will work in your case but I use powershell script to make a copy of the excel file before flow touches anything.
Basically I refresh an excel file to get fresh data, make a copy of the file and let flow get that data from the copy. Works for me.
Community Power Pla... on at

Like (2)

Report

Re: Flow breaks Excel connections to external sources

I'm experiencing this problem. I have an excel file that also queries to a web source, like baldy. Before letting flow connect to it, when I do a refresh in excel, the table in my worksheet populates with new data from the connected source, as expected.

As soon as I connect to it in Flow and the __PowerAppsId__ column is added, the connection breaks and changes to data in the connected source are no longer reflected in my worksheet table.

I'd love to know what workarounds you ended up using to get around this issue.
v-yamao-msft on at

Like (0)

Report

Re: Flow breaks Excel connections to external sources

Hi Baldy,

Could you provide your flow name and your flow id for a further investigation?

Best regards,
Mabel Mao
Community Power Pla... on at

Like (0)

Report

Re: Flow breaks Excel connections to external sources

Thanks @v-yamao-msft

I found workarounds, no worries.
Still the problem remains, and I later found out internal connections (referencing one table in another through connections within the same file) are also broken.
v-yamao-msft on at

Like (1)

Report

Re: Flow breaks Excel connections to external sources

Hi Baldy,

I am not sure if flow modifies Excel files in a way that breaks external data connections. I will take a further investigation on this issue, and I will back once I got any updates.

A temporary workaround, please use a flow to refresh your destination file A when the External file A on OneDrive is modified, so that other external data connections will not be broken.

Thanks for your understanding and patience.

Best regards,
Mabel Mao
Community Power Pla... on at

Like (0)

Report

Re: Flow breaks Excel connections to external sources

Hi @v-yamao-msft

Do you acknowledge that this is a problem?
Community Power Pla... on at

Like (0)

Report

Re: Flow breaks Excel connections to external sources

Hi,

You said that “File A has a table with external source (another file on OneDrive)”, how do you get the file content from another file on OneDrive? Are you using a flow to get the file contents?

No, I am using external data connections in Excel to do that. Maybe let's start over.
Really there are 3 files:

source.xlsx - simply one row and a few columns of data
destination.xlsx - has a table with data from source.xlsx (excel connection)
flow.xlsx - file with a Table that Flow writes into

Table in destination.xlsx has 1 row. Any time I open destination.xlsx, Excel refreshes its connection to source.xlsx and pulls fresh data from there. Once I save it, Flow kicks in and transfers that row of data from destination.xlsx to flow.xlsx. I get one new row in flow.xlsx each time flow runs.

So in simple words Flow only takes one row from destination.xlsx and adds it to the table in flow.xlsx.

Problem is, once Flow touches (via Get Rows) destination.xlsx the connection between source.xlsx and destination.xlsx is broken.
I have to go in Excel and re-establish it.

Here's how to set up the connection in Excel I'm taking about.
(Filenames on screenshots are a little different)

1. In the destination.xlsx, go to New Query -> From other sources -> From Web

2. Paste source.xlsx URL

3. Then you sign in

4. Load it in, and there you go - Excel just created a table with connection to external source.

5. Go to connections (on Data tab on the ribbon) to see if the connection works.

6. Now, save the file and run FLOW on this table in destination.xlsx. Use Get Rows and write the row to another file.

7. After it's done, go back to check the connections in destination.xlsx. It will not work.

What is even more crazy is that if I have several tables in destination.xlsx with several sources, all those connections will be broken (even if Flow only touches one!).

So in a nutshell, FLOW modifies Excel files in a way that breaks their external data connections.
v-yamao-msft on at

Like (0)

Report

Re: Flow breaks Excel connections to external sources

Hi Baldy,

You said that “File A has a table with external source (another file on OneDrive)”, how do you get the file content from another file on OneDrive? Are you using a flow to get the file contents?

I create a similar flow to get rows from File A, then insert row to File B. File A and File B are saved on OneDrive for Business. Below is a screenshot of my flow:

I run the flow for many times, but I don’t get the issue what you said that connections are broken.

After you recreate the connections, does this issue occur again?

About the column (__PowerApssId__), I have seen a thread about it, hope it can be a reference for you:
https://powerusers.microsoft.com/t5/Flow-Forum/What-is-quot-Excel-Get-Row-quot-action-s-quot-Row-id-quot/m-p/5900

Best regards,
Mabel Mao