Forum Discussion

agwalsh's avatar
agwalsh
Copper Contributor
Jun 15, 2020

Linking form responses using Power Query

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

    • agwalsh's avatar
      agwalsh
      Copper Contributor
      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. 🙂
      • yizhimoo's avatar
        yizhimoo
        Copper Contributor

        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.

Resources