Modifying collection data by grouping and transposing.

[AdamD]

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:

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?

[mdevaney]

@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."

[AdamD · Accepted solution]

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.

[mdevaney]

@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."