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:
| Name | Date | Worked Hours | Facility 1 | Facility 1 Accounts | Facility 2 | Facility 2 Accounts | Facility 3 | Facility 3 Accounts |
Blake | 5/4/2020 | 8 | Harper | 3 | Medes | 4 | | |
| Ashley | 5/5/2020 | 7 | Titus | 5 | Harper | 3 | Medes | 4 |
| Clancy | 5/6/2020 | 8 | Titus | 8 | | | | |
| Bob | 5/8/2020 | 8 | Harper | 5 | Titus | 4 | Medes | 1 |
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.
