cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdamD
Helper III
Helper III

Modifying collection data by grouping and transposing.

My company has a fitness program where if users log an activity three times a month or more, they get entered for a monthly prize drawing. I was asked to create an app to log activities now that COVID-19 has us all working and working out from home. The app is working fine, but the way the app stores information in SharePoint and the way the person who handles the drawing wants to see the information are a bit different. The table in the app looks like this:

Collection1.png

 

How I'd like to format it for a CSV export is to have a column for the NameofPerson, followed by each date being its own column, with the TypeofActivity being the field data. In other words, the table above would be formatted into a single row of data. Like this:

NameOfPerson           6/9/2020                 6/17/2020             6/25/2020
Adam                        Weight Lifting           Walking                 Hiking

I keep thinking that some combination of a ForAll statement and GroupBy will get me where I need to be, but every time I go about creating it I get lost in the weeds. Does anyone have any advice?

1 ACCEPTED SOLUTION

Accepted Solutions

No doubt your suggestion would have been the easiest and most elegant way to solve my problem, but I am very, very stubborn and wanted this all built into PowerApps and Flow. I did manage to get there.


First I collect my records from the previous month. I start by collecting them individually, and then I collect a "leader board" that groups their activities and dates into a subtable (this will be the table I run ForAll against):

ClearCollect(LastMonthLog, Filter(FullLog, StartsWith(Text(Date), Text(Month(Now())-1))));ClearCollect(LastMonthLeaderBrd, GroupBy(LastMonthLog, "NameOfPerson", "Logs"))


I clean up the LastMonthLog by dropping the unnecessary SharePoint created columns. Then I make a new collection that pulls each distinct employee from the records of last month, and I add 31 days worth of blank columns.


ClearCollect(RecordsOfMonth,ShowColumns(LastMonthLog,"NameOfPerson","TypeOfActivity","Date"));ClearCollect(EmployeeActivities,Distinct(RecordsOfMonth,NameOfPerson));ClearCollect(CSVExport,AddColumns(EmployeeActivities,"Day1","","Day2","","Day3","","Day4","","Day5","","Day6","","Day7","","Day8","","Day9","","Day10","","Day11","","Day12","","Day13","","Day14","","Day15","","Day16","","Day17","","Day18","","Day19","","Day20","","Day21","","Day22","","Day23","","Day24","","Day25","","Day26","","Day27","","Day28","","Day29","","Day30","","Day31",""))​

From here I create a final collection for export by looping through all the LastMonthLeaderBrd collection with ForAll, and for each value returned I patch it to the corresponding column in CSVExport. I then drop any columns that don't have data in them, so the person looking at the CSV file doesn't wonder why February looks like it has 31 days.

Not at all pretty, and I think I can probably reduce the amount of collections I'm passing things through, but there it is in its unrefined form.

View solution in original post

3 REPLIES 3
mdevaney
Super User
Super User

@AdamD 
My approach would be to import the data into a collection and then output to CSV.  However, there is a huge problem collection names cannot be dynamic.  Your column names would be always changing due to having different dates!  And also, the collection could have varying numbers of columns which doesn't work.

The good news is: you have used the right type of data structure here to get the desired result in ANOTHER application.  If you hook-up this SharePoint list to an Excel Pivot Table matching the desired output the user can Save As CSV after refreshing.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

No doubt your suggestion would have been the easiest and most elegant way to solve my problem, but I am very, very stubborn and wanted this all built into PowerApps and Flow. I did manage to get there.


First I collect my records from the previous month. I start by collecting them individually, and then I collect a "leader board" that groups their activities and dates into a subtable (this will be the table I run ForAll against):

ClearCollect(LastMonthLog, Filter(FullLog, StartsWith(Text(Date), Text(Month(Now())-1))));ClearCollect(LastMonthLeaderBrd, GroupBy(LastMonthLog, "NameOfPerson", "Logs"))


I clean up the LastMonthLog by dropping the unnecessary SharePoint created columns. Then I make a new collection that pulls each distinct employee from the records of last month, and I add 31 days worth of blank columns.


ClearCollect(RecordsOfMonth,ShowColumns(LastMonthLog,"NameOfPerson","TypeOfActivity","Date"));ClearCollect(EmployeeActivities,Distinct(RecordsOfMonth,NameOfPerson));ClearCollect(CSVExport,AddColumns(EmployeeActivities,"Day1","","Day2","","Day3","","Day4","","Day5","","Day6","","Day7","","Day8","","Day9","","Day10","","Day11","","Day12","","Day13","","Day14","","Day15","","Day16","","Day17","","Day18","","Day19","","Day20","","Day21","","Day22","","Day23","","Day24","","Day25","","Day26","","Day27","","Day28","","Day29","","Day30","","Day31",""))​

From here I create a final collection for export by looping through all the LastMonthLeaderBrd collection with ForAll, and for each value returned I patch it to the corresponding column in CSVExport. I then drop any columns that don't have data in them, so the person looking at the CSV file doesn't wonder why February looks like it has 31 days.

Not at all pretty, and I think I can probably reduce the amount of collections I'm passing things through, but there it is in its unrefined form.

View solution in original post

@AdamD 

We think alike.  That's a possible solution I considered before saying "just do a Pivot Table instead."  I am always making a calculation of time spent vs. value of information so that's why I decided on the 'lazy' way.  That being said, I applaud your solution and I bet you learned something by doing it.

Thanks for sharing your approach.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,258)