Forum Discussion

Blake753's avatar
Blake753
Icon for Helper II rankHelper II
6 years ago
Solved

Same Questions Multiple times in Microsoft Forms

So I have a Microsoft Form that is pumping data into an online excel sheet.  The form is gathering data from workers to see how many accounts they completed that day and for which facilities.  For example:

 

Let's say Bob completes 10 accounts for facility A and 8 accounts for facility B, then he logs that into the form which is structured to allow him to put in inputs for up to 3 facilites.  The form is branched in a way that if he only does work for 1 facility, then the form ends there.  But if he does multiple facilities, then another section follows with the same questions as before just for a different facility.  This produces an output that roughly looks like this:

 

NameDateWorked HoursFacility 1Facility 1 AccountsFacility 2Facility 2 AccountsFacility 3

Facility 3 Accounts

Blake

5/4/20208Harper3Medes4  
Ashley5/5/20207Titus5Harper3Medes4
Clancy5/6/20208Titus8    
Bob5/8/20208Harper5Titus4Medes1

 

My question is how do I take this data and create visuals in Power BI?  I realize the possibility of just submitting a different response for each facility, but we are trying to keep this as simple as possible and would like for them to only have to fill out one form a day.  Is there a better way to structure the form, do I need to perform a type of power query to turn this into multiple rows, or is there some transformation I can do in Power BI?  In it's current form I can't build proper visuals that combine like data in the same rows, or at least I think I can't.  I hope this makes sense, I will clarify as much as I can if there are questions.

 

Thank you.

  • Hi Blake753 ,

     

    This might be helpful.

    • Create an Office Group (you will have some already) to contain your Form
    • In the Forms start page switch from 'My Forms' to 'Group Forms'
    • Create a new GROUP FORM - this is the step that makes it all work...
    • Add one entry to test data capture (needed to trigger the data capture behind the scenes)
    • Go to review responses by selecting you completed form
    • The screen will present some results, but just under the Ideas icon on the right you have the option to open the results in excel - do so now..
    • This file, you can connect to using GET DATA in Power BI as it resides in a document library on sharepoint for the group you selected earlier.  I use the option to connect to a 'SharePoint Folder'  to connect and not File.
    • Away you go and design your report.

    Refer to this thread:https://community.powerbi.com/t5/Desktop/Power-Bi-amp-MS-Forms/td-p/164042/page/2 

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support

    Hi Blake753 ,

     

    This might be helpful.

    • Create an Office Group (you will have some already) to contain your Form
    • In the Forms start page switch from 'My Forms' to 'Group Forms'
    • Create a new GROUP FORM - this is the step that makes it all work...
    • Add one entry to test data capture (needed to trigger the data capture behind the scenes)
    • Go to review responses by selecting you completed form
    • The screen will present some results, but just under the Ideas icon on the right you have the option to open the results in excel - do so now..
    • This file, you can connect to using GET DATA in Power BI as it resides in a document library on sharepoint for the group you selected earlier.  I use the option to connect to a 'SharePoint Folder'  to connect and not File.
    • Away you go and design your report.

    Refer to this thread:https://community.powerbi.com/t5/Desktop/Power-Bi-amp-MS-Forms/td-p/164042/page/2 

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.