cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amanverma
Helper I
Helper I

Transpose Timesheet Collection Data in Gallery

Hi, 

Background & Solution:  Users are filling timesheet in ERP application weekly and data is stored in some database in the backend. The ERP system doesn't have timesheet adjustment feature (change the dates, hours or customer) therefore, team has to manually feed the data in system which takes a lot of their times.

 

We are developing a solution using PowerApp to automate this manual task. Data, we are getting directly from database table. Below is the schema. 

 

 

CustomerTimeTypeTimesheetDateHoursStartDate
C1A1/8/202281/8/2022
C2D2/8/202231/8/2022
C3C2/8/202251/8/2022
C4A3/8/202281/8/2022
C2A4/8/202251/8/2022
C3D4/8/202231/8/2022
C5SS5/8/202281/8/2022

 

I am trying to show the Data in below format. In header customer name and week days and dates (1-5). Customer column holds the customer name, day/ date column holds the hours. 

 

CustomerTimeTypeMon 01Tue 02 Wed 03Thu 04Fri 05
C1          A8    
C2          B 3 5 
C3         C 5 3 
C4      D  8  
C5A    8

 

I have managed to get the data in this format but having hard time to get the duplicate customers eliminated and set the hours accordingly. 

Any help would be highly appreciated. 

2 REPLIES 2
v-xiaochen-msft
Community Support
Community Support

Hi @amanverma ,

 

I made a sample for you.

vxiaochenmsft_0-1660013085650.png

vxiaochenmsft_1-1660013134826.png

DropColumns(
    AddColumns(
        AddColumns(
            AddColumns(
                AddColumns(
                    AddColumns(
                        GroupBy(
                            AddColumns(
                                ShowColumns(
                                    LIST260,
                                    "Customer",
                                    "TimesheetDate",
                                    "Hours",
                                    "StartDate"
                                ),
                                "Num",
                                Weekday(
                                    TimesheetDate,
                                    2
                                )
                            ),
                            "Customer",
                            "Test"
                        ),
                        "Mon 01",
                        If(
                            1 in Test.Num,
                            Sum(
                                Filter(
                                    Test,
                                    Num = 1
                                ),
                                Hours
                            )
                        )
                    ),
                    "Tue 02",
                    If(
                        2 in Test.Num,
                        Sum(
                            Filter(
                                Test,
                                Num = 2
                            ),
                            Hours
                        )
                    )
                ),
                "Wed 03",
                If(
                    3 in Test.Num,
                    Sum(
                        Filter(
                            Test,
                            Num = 3
                        ),
                        Hours
                    )
                )
            ),
            "Thu 04",
            If(
                4 in Test.Num,
                Sum(
                    Filter(
                        Test,
                        Num = 4
                    ),
                    Hours
                )
            )
        ),
        "Fri 05",
        If(
            5 in Test.Num,
            Sum(
                Filter(
                    Test,
                    Num = 5
                ),
                Hours
            )
        )
    ),
    "Test"
)

 

DropColumns(AddColumns(AddColumns(AddColumns(AddColumns(AddColumns(GroupBy(AddColumns(ShowColumns(LIST260,"Customer","TimesheetDate","Hours","StartDate"),"Num",Weekday(TimesheetDate,2)),"Customer","Test"),"Mon 01",If(1 in Test.Num,Sum(Filter(Test,Num=1),Hours))),"Tue 02",If(2 in Test.Num,Sum(Filter(Test,Num=2),Hours))),"Wed 03",If(3 in Test.Num,Sum(Filter(Test,Num=3),Hours))),"Thu 04",If(4 in Test.Num,Sum(Filter(Test,Num=4),Hours))),"Fri 05",If(5 in Test.Num,Sum(Filter(Test,Num=5),Hours))),"Test")

Best Regards,

Wearsky

amanverma
Helper I
Helper I

Hi @v-xiaochen-msft - Thank you very much for the code. 

 

At this stage, I have already completed the task using a different method, however, i believe your approach is better than mine. 
I am guessing, once i have this code on my Gallery.Items, all i have to do is call "Mon 01",  "Tue 02"......"Fri 05" on hour fields?

 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (2,506)