Real Time Data Set using MS Forms, MS Flow, and Power BI.

[DcordJE]

I'm trying to create a dynamic Dashboard using MS forms, the form is currently built within MSForms and is being used. In today's world I am pulling the report to Excel periodically, copying the data over to another Excel workbook where I already have my report built out. I hit refresh and filter out the data I need. This is v ery manual and uses numerous formuals. I am trying to link the current form intack to Power BI via MSFlows.

I have created the DataSet on the PowerBI site under "my workplace". I used all entries on the form and selected whether the data was a numer, Text or date. I have 25 entry points ( questions on the form), excluding the automated values like ID, Start Time, Completion Time, Email, and Name. I selected the Historical Data On option and hit save.

I went on to create my flow and linked it as  MSform-"When a new response is submitted".  under "Get Response Details" I added the form ID, and under the Form Id I added the following expression: first(triggerBody()?['value'])?['resourceData']['responseId']

Then I selected "Apply to each", PowerBI-Add Rows to a dataset.  Under PowerBI-Add Rows to a dataset I selected "myworkspace , the dataset I created, and the table as RealTimedata ( autopopulated selection), I matched the entry options from the form to the ones I created in the dataset ( I labelled them the same for an easy pairing).

Then back in the PowerBI site I tried to creat the report within my workspace. There are 2 issues I am confronting:

1. The report only pulls newly entered data. I have about 4000 enteries that were entered previously that aren't pulling.

HOW DO I CONNECT THE OLDER DATA TO THIS FLOW?

2.The report doesnt seem to give me an option to pull the automated values that are uploaded onto MSForms when an entry is submitted.

HOW CAN I GET THE AUTOMATED NAME VALUE FROM THE FORM?

Also is there a way to do the flow using Excel instead of MS Forms?

[v-alzhan-msft]

Hi @DcordJE ,

Could you please share a screenshot of the configuration of your flow?

For the first problem,you could only get the new data which trigger the flow in microsoft flow currently, there is no any way to get the older data except that you save the older data in a excel table or sharepoint list, then you could get all the data from the excel table with "List rows present in a table" action or get all the data from the sharepoint list with "get items" action, then you could apply to each record to save the data in your report excel.

For the second problem, could you please explain more details about the second problem? how to understand the "pull the automated values" that you mentioned?

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DcordJE]

                                                                                                                                                                         Hello Alice,

Please see the Screen shot above.

Currently the Forms auto save the data onto an Excel spreadsheet on my Onedrive. Could you share a link or outline the steps on how the  get the data from the excel Table?

Ok for the second problem I'll give you an example:

My form currently starts by asking what Region the entry is for, then Market, then location and so on.

When I open the excel version of the data I see the following:

ID number, Start Time, End Time, The person's name that entered the dtata, The person's email that entered the data and then Region, Market, location and so on.

I need to know who entered the data for reporting (I.E 100 entries where completed by John)