Answered

How to store and tally multiselect field data from PowerApps in a Sharepoint List?

(0) Share

Report

Posted on by CG-07102153-0

I'm building a Canvas App in PowerApps that uses multiple Sharepoint Lists as data sources. The app is used to help team members track a variety of KPIs, including - for example - the countries in which they have visited clients in a given year. The data generated from this App will be displayed on a dashboard that shows, for example, a map of all countries visited by the team in a given year, with different shading according to how many team members visited a country that year. For this, I will need to be able to tally the data like so:

I see 2 scenarios for how to store & manage the data.

Scenario 1: easy to implement the data input/update process, but difficult to use the data for displaying on a dashboard:

The easiest way for me to implement the data input/update process in PowerApps/Sharepoint Lists (in terms of setting up the form, creating and editing records) appears to be setting up a multiselect field that lets the user select (multiple) countries. I can then patch this selection into a Sharepoint List, resulting in 1 row per user, with all selected country values stored in 1 cell.

Scenario 2: more difficult to implement regarding data input/updating (for me), but easy to tally

The data from the multiselect field will have to be "spread" across individual rows (1 row per country), which allows for easier tallying of the data. However, the patch routine for entering & updating data will be more complex. Also, this will result in a very long table which will bring me up against query limits much sooner.

I would prefer Scenario 1 because I already know how to set up a form + patch routine, but I'm not sure how I would go about processing the data from the 'CountriesVisited' column to create the tally I need for my dashboard.

Any advice on how to proceed would be highly appreciated.

All responses (3)

Answers (1)

Verified answer

WarrenBelz 143,297 on at

Like (0)

Report
How to store and tally multiselect field data from PowerApps in a Sharepoint List?

Hi CG-07102153-0

It really depends on what you want to do with the data - simply display and report or keep it for the future). If you want distinct totals of each country visits per year
RenameColumns( AddColumns( GroupBy( Ungroup( AddColumns( SPList, Data, Split( CountriesVisited, "," ) ), Data ), Value, Year, Grouped ), Visits, CountRows(Grouped) ), Value, Country )

Just one thing to be aware of - none of that is supported by Delegation (every action in there is a "local" function and restricted by your Data Row Limit ).

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee
CG-07102153-0 23 on at

Like (0)

Report

How to store and tally multiselect field data from PowerApps in a Sharepoint List?

This looks very promising, @WarrenBelz, thank you so much.

If I understand correctly, the code you propose generates a new Table formatted as Scenario 2 inside the PowerApps app, where I'm less familiar with manipulating data.

Therefore, I have one follow-on question, just so that I know how best to proceed:

Is it possible and/or advisable to (a) build on the code you shared and further manipulate the table data inside the app to create the tally I need (distinct country values and the # of times each value appears in a given year), or (b) do I need to patch the "Scenario 2 table" back to a Sharepoint List or another source type to do further manipulation there (i.e. to create the tally, which will ultimately serve as the basis for creating a map).

Thanks so much again!
WarrenBelz 143,297 on at

Like (1)

Report
How to store and tally multiselect field data from PowerApps in a Sharepoint List?

Hi CG-07102153-0

This should turn a list based on Scenario 1 into a Table formatted as Scenario 2 - is this what you are requiring ?
ShowColumns( RenameColumns( Ungroup( AddColumns( SPList, Data, Split( CountriesVisited, "," ) ), Data ), Value, Visited ), Name, Year, Visited )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee