Building a Collection from multiple lines of data

(1) Share

Report

Posted on by Hack-7

216 Super User 2025 Season 1

My issue today is working with a set of data that has the following format.

ProjectID	weekNumber	percentTime
2024-REQ-001	28	10
2024-REQ-001	29	5
2024-REQ-001	30	2
2024-REQ-001	27	15

What I need to do is have a collection by ProjectID that has columns for weekNumber and the percentTime in that field. So it would look something like this. Yes, I realize I will be building a line for each of the weeks of the year unless there are other thoughts.

ProjectID	week28	week29	week30	week27
2024-REQ-001	10	5	2	15

This is the code I wrote and it is not the desired results

ClearCollect(
 colTimeTracker,
 ShowColumns(
 AddColumns(
 TimeTracker,
 pTitle, Title,
 week28, LookUp(pmoTimeTracker, Title = TimeTracker[@Title] And weekNumber = 28, percentTime),
 week29, LookUp(pmoTimeTracker, Title = TimeTracker[@Title] And weekNumber = 29, percentTime),
 ),
 pmoTitle, week28, week29
 )
);

Maybe I am approaching this all wrong! Any help is appreciated.

Glen

All responses (2)

Answers (1)

Verified answer

Hack-7 216 Super User 2025 Season 1 on at

Like (0)

Report
Re: Building a Collection from multiple lines of data

I was able to create a solution that worked. Not sure if it was the best way to do it but it works.

Here is a Snip of the code for reference if someone wants it:
ClearCollect( colTimeList, AddColumns( GroupBy(TimeTracker, projectID, results), pm, LookUp(Projects, Title = projectID).pm, AFE, LookUp(CostObjects, projectID = projectID, costObject), ProjectDetails, LookUp(Projects, Title = projectID).projectTitle, week1, If(IsBlank(LookUp(results, weekNum = 1).percentTime), 0, LookUp(results, weekNum = 1).percentTime), week2, If(IsBlank(LookUp(results, weekNum = 2).percentTime), 0, LookUp(results, weekNum = 2).percentTime),
I had to use the If() statement in each of the week#s since JSON to CSV does not like NULL values and not all of the fields had data.
developingteal 392 on at

Like (0)

Report

Re: Building a Collection from multiple lines of data

definitely use a collect, and then loop on it for each row, clear it whenever you need to. Clearcollect seems like it doesn't meet your needs/what you are trying to do