Linking form responses using Power Query

Copper Contributor

I want to have a Form linked directly into a Power Query Excel file so that when responses are added,  the data source can be automatically refreshed and the user can see the new pivot table. I have tried linking the Forms link via From Web (didn't pick up the data in the form). Tried the same thing with the link that gives the summary of responses - same problem - got stuff about Document and then Kind/Name/Children/Text. What am I missing? I searched for this but just got something about embedding in a webpage? Thank you

4 Replies

same help needed here too

Just so you know, I ended up with a clunkier solution of downloading the new file into an Excel file that had the cleansing on it and refreshing. it's not ideal - but given the alternative option the user was going to do, much better. 🙂

@agwalsh I learnt from the section Creating a Form in SharePoint, linked as the text. It was clueless how to form a live response data collection until I came across this article. Before starting any edit, please Open in Desktop App. I managed to create an excel data file with queries on SharePoint link with my form responses at the first tab. Using Power Query as a second tab for table calculation and total score evaluation. After completing all the data analysis, remember to autosave so it will sync the data back SharePoint. Hope this helps.

@yizhimooThank you very much for that - it's really useful. That's a well explained article - oddly enough I used something else from that website yesterday for a tutorial on SharePoint 🙂