GroupBy a Filter in a Gallery - I tried, but got stuck

[LSB]

Hi, 

I have a Dataverse table that looks like this:

I want to navigate through the records with a gallery. Everything works fine, but I got stuck on a problem in the yellow marked data set.

As you can see the values in E2 are duplicate. And I want only one of it shown in my filtered gallery.

Here is the process in detail:

On screen 1 the table is grouped by the column E1 (or crd55_e1, that is the real name of the column).

If I click on an item, we navigate to screen 2.

The first thing that happens, is that the selected items ID (E1_ID) ist stored in the variable varE1.

The gallery is then filtered using this variable. 

Filter('FAQ Dosiertechnik';StartsWith(E2_ID;varE1))

And here we got the two same records, like I mentioned earlier in the table view.

I read different threads here in the forum like this: Solved: Combine filter and group by in power apps - Power Platform Community (microsoft.com)

And tried to groupby my filtered gallery. I ended up with different formulas, that all did not work. Nothing shows up in the gallery.

For example:

Do I need a second gallery to show the grouped data?

Can I achieve the same result with distinct() ?

What is the bit I am missing?

I hope you have a hint for me, because I have a headache and am not able to think straight 😄

Thanks a lot.

[aka271 · Accepted solution]

Hey 🙂

You could try to use the Distinct( Table, Formula )-Function.

https://learn.microsoft.com/de-de/power-platform/power-fx/reference/function-distinct

Have a nice Day! 🙂

[LSB]

I tried it with

Distinct(Filter('FAQ Dosiertechnik';StartsWith(E2_ID;varE1));E2)

But the gallery is empty now. Do I need to change my Text property? Does it not reference to "ThisItem" anymore If use Distinct or GroupBy?

[aka271]

Looks like your Code has some Errors. Can you share the Errors with me? 🙂

Also, If you didn't know by double clicking on a Function/Variable (Filter() for example) you can see the values that are returned. Maybe it helps you to find the Error 🙂

[LSB]

Hi aka271,

thanks for the hint with the values. 

Here is the comparison between the initial filter and the distinct filter.

The result looks quite promising. 

The errors are all the same acutally. Different elements have now problems with an argument in the visibity function.

I think it is the same thing why the gallery is not showing anything. I need to reference to "Value" now, instead to ThisItem. But I do not get the syntax (or the basic idea behind it) right.

Edit: And here ist what I get when I use the groupBy instead of distinct.

Maybe this is better, because I get the chance to get the other columns aswell, because they are stored in the "NewData" table now?

[aka271 · Accepted solution]

Great! 🙂

"ThisItem.Value" instead of "ThisItem.E2_ID" should fix the Problem with your Items not showing up! 🙂

[LSB]

Yes it did. 🙂

But the problem is, that I do not check the "Value" (former E2) column in the visibility function, but the E2_ID.

So I think I should go the way with groupedBy instead of distinct. Because as far as I unterstand it, the rest of the colums will be stored in a new "virtual" table, and can be accessed.

I edited my previous answer to add the groupedBy output

[aka271]

Great! 🙂

So everything is working now? 🙂 I dont have much experience with the groupedBy-Function. But if it works and you can access all the Data you need, go for it! 🙂

[LSB · Accepted solution]

I got it working with groupBy.

I needed to use addcolumn to get the E2_ID column out of the "NewData" table.

And then I was able to reference it correctly in the visibility functions.

And you also can see the filtered gallery 🙂

Thanks a lot aka271 for guiding me in the right direction.