Skip to main content

Notifications

Community site session details

Community site session details

Session Id : ypSIfSMqW+POEL9rA7KIel
Power Apps - Building Power Apps
Answered

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

Like (0) ShareShare
ReportReport
Posted on 12 Nov 2024 09:48:15 by 23
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.
  • Verified answer
    WarrenBelz Profile Picture
    146,788 Most Valuable Professional on 12 Nov 2024 at 21:27:10
    How to store and tally multiselect field data from PowerApps in a Sharepoint List?
    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 Profile Picture
    23 on 12 Nov 2024 at 20:42:19
    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 Profile Picture
    146,788 Most Valuable Professional on 12 Nov 2024 at 12:11:24
    How to store and tally multiselect field data from PowerApps in a Sharepoint List?
    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

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Apps - Building Power Apps

Overall leaderboard
Loading started