Gallery GroupBy error: The first argument to the groupby function cannot be blank

[Copycat]

Good day,

I need a help with above error or help me understand what approach should I follow.

App use case:

This app allows data stewards to add comments into Power BI report, save them on SQL table and present comments for management.

Technical details:
Connector: PowerBIIntegration.Data

Current Gallery source: 

Distinct(
    PowerBIIntegration.Data, 'Customer Code')

Issue:

I had to add additional column from Power BI (key_column) which duplicated my Customer Code rows in PA, since for each Customer Code, there can be multiple keys.

I'm using distinct to get only non duplicated rows but I also need to sort this gallery by another column called 'Control' so the record with "Reference" value, will be always on top.

Here starts the problem, I have no idea how to show 2 and more (approx 25) columns in the Gallery, with distinct values. I can't use Collection, since it disables the PowerBIIntegration.Data from being "live" connection.

Attempts:

I was trying to use:

GroupBy(
    PowerBIIntegration.Data, "Customer Code", "Control", "Data")

So it would get Customer Code, Control and everything else would be stored as a table in Data, but this gives me an error 

The first argument to the groupby function cannot be blank

My second attempt was to create one overall gallery with all records and 2 galleries with helper columns: distinct Customer Code, and filtered Control but this causing more mess and app starts to work really slow.

Any ideas?

[mmbr1606]

hey @Copycat 

try the following:

// On App Start or appropriate trigger
ClearCollect(
    ColSortedData,
    Sort(
        ForAll(
            Distinct(PowerBIIntegration.Data, 'Customer Code'),
            LookUp(PowerBIIntegration.Data, 'Customer Code' = Result)
        ),
        'Control', Descending // Or Ascending based on your requirement
    )
);

// Set the Gallery's Items property to:
ColSortedData

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[Copycat]

Good day,

thank you for your replay!
Unfortunately it is not working because of two issues:

1. Collection disabled PowerBIIntegration live connection, but I managed to solve it - details below.
2. It gives me an error that I cannot compare Error, Text.

Inspired by your solution, this is what I did:

ClearCollect(
    SortCollection,
    SortByColumns(PowerBIIntegration.Data,"Control",SortOrder.Descending)
    );

ClearCollect(
    DistinctCollection,
    Distinct(SortCollection, 'Customer Code')
    );

Then my gallery items are setup to DistinctCollection.

I was unable to fit within one collection Sorting and Distinct, so I made two collections for each of the required steps.

Then I added a button to create these collections in the app, so every time users need to refresh the data, they need to use my button to see new set of data.

It's not perfect solution but it is what it is.