MS Forms -> Excel -> PowerBi

(1) Share

Report

Posted on by FE-20020846-0

Dear community,

we do use several MS Forms which write the replies into excel files on sharepoint (standard MS Forms setup). Then we retrieve this information via PowerBi to visualize the results.

This was working fine last year, then there was a message that the files will need to be updated since something changed in the syncronization.

The result is, that the excel files do not update automatically anymore, only if a user physically opens the excel file, the data will be written into the file.

Since I want to keep the existing excel file instead of making a PowerAutomate workaround to write that into a different excel file (which actually works) - my question would be if there is a PowerAutomate workflow that updates the existing excel file once every hour or similar?

Thanks in advance for your help

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Michael E. Gernaey 53,969 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi,

I am not following. Can you please share pictures of what you have please.

Another option would be to either write whatever you want to another master excel, or you could write it to SharePoint and point POwer BI to that.

All of this could be done for sure in Power Automate

1. Create an Automated Flow

2. Select the When a Response is Submitted.

3. Add a Get Response Details, where the Input is the Dynamic Properties of step 2

4. Write the data to a Master excel or SharePoint list and point Power BI to that.

I am unaware of having to open the file etc in Forms, but that stinks if thats how it is now. The above will help. Here is a quick view of the flow Im referring too.

Now there are a few minor things, like you need to have an Excel Table around the data in the Excel Master Sheet if you use that. Other than that... its pretty much as simple as the below (but I do recommend adding error handling)

Step 1 and 2.

Step 3.

Step 4.

Was this reply helpful? Yes No
FE-20020846-0 2 on at

Like (0)

Report
Copy link

Link copied!

Hi,

as described, building a powerautomate to write replies into a new excel works. But that's a workaround for me. And I do have already plenty of data in the existing excel files which I don't want to transfer into another excel file.

We do have several MS Forms with the automatically connected excel files already (As soon as you create a MS Forms, the system creates the excel on sharepoint to write the replies of the MS Forms). Those excel files updated automatically in the past, right now, they do not anymore. So what happens: as soon as there are several replies on the MS Forms and you open the excel files, it takes a couple of seconds for the excel file to retrieve the answers, which then will be added to new rows in the file. That means, without opening the file manually, I could not retrieve the date into PowerBi because the data is not yet written into the excel file.

So what I would like to understand: Is there a power automate that virtually opens the excel file so that the MS Forms replies can be written into the excel file. So if I would open the excel file, the results are already present. That means, the data is present in the file instead of having to be loaded by manually open the excel file.

I would not know which picture I could post.

Was this reply helpful? Yes No
Michael E. Gernaey 53,969 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi

You could use Power Automate Desktop to open it. I don't know if using Get File contents would make that work.

Did you try that yet?

So either Desktop, or try get file content from sharepoint and see if that works.

Was this reply helpful? Yes No