Forum Discussion
Breaking links to a query as it updates
Hi,
I am using some data that is being generated by people filling out a MS Form on an ongoing basis.
Once the data is generated, I need a manager to be able to enter some additional data to each row and I need to be able to sort both based on the original data from the form and on the new data entered by the manager.
My problem is that I want new rows to continue to be added when new submissions are made via the form, however, I don't want the data that the manager has entered after the fact to be wiped out when the query refreshes.
Basically I want to be able to break the link to the data once it is pulled into the manager's dashboard, but also maintain the link so that new entries continue to be added. Is this possible?
Really appreciate the help!
Do I understand correctly manager adds additional data to the same rows which Form generated? If so you may simply add few other columns to the table which Form uses, assuming Form works with Excel file located on Sharepoint. Form and manager could work simultaneously with the same table, that's kind of co-authoring. Form always adds new row to the bottom of the table ignoring manually added columns, no managers's info will be affected.
If Form works not with Sharepont file when flow. Same story, you may add additional columns which Flow ignores adding next rows. The only flow is less reliable.
7 Replies
I accomplished something similar by creating an additional table then merging or appending (depending on your desired output) to the Fact Table using Power Query.
Basically Merging/Appending Table 1 and Table 2 to make a Table 3.
Is that what you are needing?
ChrisMendoza Thanks!
Yes I believe that sounds like it could would work, although I'm not familiar with Power Queries, Is that part of the standard Excel package?
If you have a newer Excel Version, yes. I've never used Forms so I took a quick peek.
Is your process to download the Responses to Excel (a static file)?
Seems like you would want to take a look at Power Automate to get a flow going. I'm sure you can find a template @ https://us.flow.microsoft.com/en-us/search/?q=forms that will write the responses to an Excel file as they are submitted. That would be more dynamic for sure. Then you can use that as Table 1 Create your Table 2 to Merge/Append.
Take a look at https://www.youtube.com/watch?v=NJEvr5ZoEEw ; something along these lines.
