cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lknudson
Level: Powered On

Aggregate table into collection grouping on 2 columns adding additional columns based on 3rd column

I have a sharepoint table with 4 columns:

User, Date, TaskType, Hours (TaskType is fixed number of 4 values: Sweep, Dust, Wash, Wax)

 

So, records like:

Jim, 1/1/2018, Sweep, 2

Jim 1/1/2018, Wash, 4

 

I would like to produce a Collection with resulting records:

User, Date, TaskType1, TT1Hours, TaskType2, TT2Hours, TaskType3, TT3Hours, TaskType4, TT4Hours

Like:

Jim, 1/1/2018, Sweep,2,Dust,0,Wash,4,Wax,0

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Aggregate table into collection grouping on 2 columns adding additional columns based on 3rd col

Hi @lknudson,

 

Please take a try with the following format and see if it could help in your scenario:

AddColumns(
    RenameColumns(
        RenameColumns(
            DropColumns(
                GroupBy(
                    TestData,
                    "user",
                    "Date",
                    "Records"
                ),
                "Records"
            ),
            "user",
            "User1"
        ),
        "Date",
        "Date1"
    ),
    "Task1",
    "Sweep",
    "TT1Hours",
    Sum(
        Filter(
            TestData,
            user = User1,
            Date = Date1,
            Task = "Sweep"
        ),
        Hour
    ),
    "Task2",
    "Dust",
    "TT2Hours",
    Sum(
        Filter(
            TestData,
            user = User1,
            Date = Date1,
            Task = "Dust"
        ),
        Hour
    ),
    "Task3",
    "Wash",
    "TT3Hours",
    Sum(
        Filter(
            TestData,
            user = User1,
            Date = Date1,
            Task = "Wash"
        ),
        Hour
    ),
    "Task4",
    "Wax",
    "TT4Hours",
    Sum(
        Filter(
            TestData,
            user = User1,
            Date = Date1,
            Task = "Wax"
        ),
        Hour
    )
)

 

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Aggregate table into collection grouping on 2 columns adding additional columns based on 3rd col

Hi @lknudson,

 

Please take a try with the following format and see if it could help in your scenario:

AddColumns(
    RenameColumns(
        RenameColumns(
            DropColumns(
                GroupBy(
                    TestData,
                    "user",
                    "Date",
                    "Records"
                ),
                "Records"
            ),
            "user",
            "User1"
        ),
        "Date",
        "Date1"
    ),
    "Task1",
    "Sweep",
    "TT1Hours",
    Sum(
        Filter(
            TestData,
            user = User1,
            Date = Date1,
            Task = "Sweep"
        ),
        Hour
    ),
    "Task2",
    "Dust",
    "TT2Hours",
    Sum(
        Filter(
            TestData,
            user = User1,
            Date = Date1,
            Task = "Dust"
        ),
        Hour
    ),
    "Task3",
    "Wash",
    "TT3Hours",
    Sum(
        Filter(
            TestData,
            user = User1,
            Date = Date1,
            Task = "Wash"
        ),
        Hour
    ),
    "Task4",
    "Wax",
    "TT4Hours",
    Sum(
        Filter(
            TestData,
            user = User1,
            Date = Date1,
            Task = "Wax"
        ),
        Hour
    )
)

 

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lknudson
Level: Powered On

Re: Aggregate table into collection grouping on 2 columns adding additional columns based on 3rd col

Thanks very much for your example!  I used your sample and when I ran it, I got an error on "Sum" function.  Apparently it doesn't work in SharePoint connecter?  So, I first pulled my SharePoint list into a Collection, then used that with your example, and it looked good.  I am adding some changes so that if a Task has no hours, it will default to zero instead of just be missing.  Then I need to do some summations on the daily totals per Task for a range of dates.

But thanks againI  I had lost my way trying to keep track of Add/Rename/Drop/Group orders.

 

Regards,

Larry