Combine excel data and microsoft form data on powerbi

[Anonymous]

I want to create a control chart that receives and populates data from a microsoft form. I was able to do this but I also have data from an excel file that I would also like to include that was used before the creation of the form. I tried creating a query for each but was not able to use both sets of data in the control chart. Each question on the form matches a column on the excel.

[ChrisMendoza]

@Anonymous -

Are you manually downloading the Excel file from MS Forms or are you using Power Automate to write to Excel Online (or similar?).

My point being that if the existing old Excel file matches columns, then why not convert the old into an Excel Online file and use Power Automate 'On form submission' to 'Add a row to table'; thereby consolidating old with the new submissions (one table).

You could append old to the new file of course as well in Power Query however, I think it would just be better to do as above.

[Anonymous]

Do you have an article that demonstrates the steps for power automate?

[ChrisMendoza · Accepted solution]

@Anonymous - I don't have one for MS Forms + Power Automate + Excel Online. This post describes Planner + Power Automate + Excel Online which would be similar https://powerusers.microsoft.com/t5/Process-Advisor/Log-new-tasks-created-in-Planner-in-Excel-Online-Business/m-p/928328#M256 .

Reza Dorrani is a great resource I use -> https://www.youtube.com/watch?v=kbqXsKPTmZk is talking about what you may need however, SharePoint is his destination; the same concept could be applied to Excel Online. There are also a ton of other YouTube videos that describe how you can accomplish.

I did just find this -> https://www.youtube.com/watch?v=qbvjYjZyQBY which is a 'Live' Excel file from Forms; I never knew about this. It doesn't really help you since you have existing data; maybe for anyone starting with a fresh Form for data collection.