Power Platform Community Forum Thread Details

Hi all, I'm trying to do the following of grouping by Dept and summing up the non-empty values in the remaining columns,

So far this is what I have and while it works, it is really inefficient especially as the number of remaining columns grows so it would be great if it could be optimized. Any help is much appreciated!

ClearCollect(deptList, {Dept: "A"}); 
Collect(deptList, {Dept: "B"}); 

ClearCollect(
    testDB0,
    AddColumns(
        GroupBy(
            Filter(
                'Driving Permits',
                'EP 1' <> ""
            ),
            Dept,
            Grouped
        ),
        Count,
        CountRows(Grouped)
    )
);
ClearCollect(testDB1, AddColumns(deptList,'EP 1', LookUp(testDB0,testDB0[@DEPT]=deptList[@Dept],Count)));

ClearCollect(
    testDB2,
    AddColumns(
        GroupBy(
            Filter(
                'Driving Permits',
                'EP 2' <> ""
            ),
            Dept,
            Grouped
        ),
        'EP2',
        CountRows(Grouped)
    )
);

ClearCollect(
    testDB3,
    AddColumns(
        GroupBy(
            Filter(
                'Driving Permits',
                'EP 3' <> ""
            ),
            Dept,
            Grouped
        ),
        'EP3',
        CountRows(Grouped)
    )
);


ClearCollect(finalDB, AddColumns(testDB1,EP2, LookUp(testDB2,testDB2[@DEPT]=testDB1[@Dept],'EP2')));
ClearCollect(finalDB1, AddColumns(finalDB,EP3, LookUp(testDB3,finalDB[@Dept]=testDB3[@DEPT],'EP3')));

Categories:

General Questions

Using Apps Using Formulas