Announcements
Hi all,
I have a data set on sharepoint that is updated quarterly, and I want to try and make the analysis more streamlined. I wondered if there was an easy way to automate what I need to do so that I can put a final product into powerBI that leadership can use. Any help in the steps that would be needed would be greatly appreciated! I think I have the rough steps in terms of the analysis, just not how to get that into an automated function or what programmes/steps would be needed that I wouldn't have to create pivot tables each quarter and copy and paste. I have access to excel, powerBI and powerAutomate if that helps at all.
The data format as I get it in excel is akin to (pseudo data as I work in social care but variable groupings the same concept):
ID |
Team |
Question1 |
Question2 |
Question3 |
Question4 |
1 |
East |
Yes |
Yes |
No |
No |
2 |
East |
Yes |
No |
Yes |
Yes |
3 |
Noth |
No |
Yes |
No |
Yes |
4 |
West |
No |
Yes |
Yes |
No |
5 |
South |
Yes |
No |
No |
No |
What I'd like to get out of it is the percentage Yes/No for each question, split by Team (perhaps something like below for % 'yes' responses?).
Team |
Question1 |
Question2 |
Question3 |
Question4 |
East |
20% |
50% |
60% |
10% |
Noth |
60% |
80% |
50% |
90% |
West |
10% |
20% |
30% |
50% |
South |
90% |
40% |
60% |
50% |
This to then go into a larger data set that holds the information for all quarters.
Quarter |
Team |
Q1 |
Q2 |
Q3 |
Q4 |
1 |
East |
20% |
50% |
60% |
10% |
1 |
Noth |
60% |
80% |
50% |
90% |
1 |
West |
10% |
20% |
30% |
50% |
1 |
South |
90% |
40% |
60% |
50% |
2 |
East |
20% |
50% |
60% |
10% |
2 |
Noth |
60% |
80% |
50% |
90% |
2 |
West |
10% |
20% |
30% |
50% |
2 |
South |
90% |
40% |
60% |
50% |
This would then go into powerBI in such a format that I could create a line graph for each question tracking change over the quarters, and can be filtered by Team (and an all teams option).
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.