Using Distinct, Filter and Concat on a text label (not Gallery)

[AlexTheKidd]

I am currently struggling with using Distinct.

What I'm trying to do: display all locations where a certain criteria is met (project number), but if there are multiple items in one location, then display the location once - example:

"4555 located at T05, T04"

However, the result I'm currently getting is:

"4555 located at T05, T05, T05, T04"

The data is from a SharePoint List.

My current code:

SP_P01 & " located at " & Concat(
    Filter(
        Deliveries,
        Status.Value = "Transport ordered",
        ShowOnPickPackApp = true,
        SPnumber = SP_P01.Text,
        StartsWith(
            yardLocation.Value,
            "P"
        ) || StartsWith(
            yardLocation.Value,
            "T"
        )
    ),
    yardLocation.Value,
    " & "
)

When running this code, it displays the same location multiple times (if all criteria are true - see example above). I've been trying to use Distinct, but when I do, it's not able to distinct the 'yardLocation', so I'm not even sure I'm putting the distinct in the right location.

Any help would be greatly appreciated!

[v-jefferni]

Hi @AlexTheKidd ,

Please try and use below formula:

SP_P01 & " located at " & Concat(
    Distinct(
        UnGroup(
            Filter(
                Deliveries,
                Status.Value = "Transport ordered",
                ShowOnPickPackApp = true,
                SPnumber = SP_P01.Text,
                StartsWith(
                    yardLocation.Value,
                    "P"
                ) || StartsWith(
                    yardLocation.Value,
                    "T"
                )
            ),
            "yardLocation"
        ),
        Value
    )
    Result,
    " & "
)

Best regards,

[AlexTheKidd]

Hi @v-jefferni ,

Unfortunately, it's stating that UnGroup is an unknown or unsupported function.

When I tried to type it all out manually;

1. The StartsWith function is not able to find the 'yardLocation', which is a dropdown selector.

2. The 'yardLocation2' is a text-value, copied form 'yardLocation', as a test, which is why it can be seen in the StartsWith function.

3. Inside the 2nd argument in the UnGroup, it's not able to identify either 'yardLocation', nor 'yardLocation2' ('yardLocationLabel' is a general text-label in a gallery on another screen, so it cannot be used).

This turned out to be a lot harder than I'd have thought...

[v-jefferni]

Hi @AlexTheKidd ,

Actually, looking at your formula, I considered yardLocation as a column name in the Deliveries table/list. The whole logic is as follows:

1. Using Filter to get a table that criteria met. For the criteria, StartsWith function will use for getting records which column X starts with some texts: StartsWith(ColumnX, "start texts").

2. I think the yardLocation column is a LookUp column and allows multiple selections. So, this column includes field values which are of type table and having a Value column in them. Here is the reason of using UnGroup function, it will help extract all the Value column values out from the yardLocation column, and now you will have a table with single column "Value", where includes all yardLocation column values.

3. Use the Distinct function to get the table with distinct yardLocation values.

4. Concatenate this table's Result column values with the & operator.

So, if anywhere of your scenario is different, please clarify and explain more. Maybe you could provide some screenshots of the table including some sample data.

Best regards,

[AlexTheKidd]

Hi @v-jefferni ,

yardLocation is the column name.

You are correct with statement 1.

2: yardLocation is a choice column from the SP list. Technically, multiple choices can be done, but through the app, only one selection will be used (we're not doing any manual data through the SP list). 

The choices are done because the yard locations change randomly when needed/we expand, so I made it a choice column so that it can be changed through the SP list, rather than hardcoding the values in PowerApps (which we did before).

3. Absolutely correct.

4. Again, correct.

From what you've written, I would say you are correct in the logic.

Here's a screenshot of the columns (I obfuscated some of the values, as I just want to make sure that I'm not sharing anything that cannot be shared). Of course, there's a lot more columns, but since these are not used in the code, I've left them out.

I might just try to refactor the code I've made until now, as I might be able to do it some other way... If I do, I'll make sure to update this post.

On this screenshot, the left is what I currently get, and the right is what I want to achieve (don't worry about the duplication of the two bottom rows, this is intended).

[v-jefferni · Accepted solution]

Hi @AlexTheKidd ,

I also have a Choices column which allows multiple selections in my SP list:

and it's easy to get all selected values concatenated together using above formula:

For your scenario, if you only need the column allow single selection, then you should set it up in SharePoint list and then it will be quite easy to use below formula:

SP_P01 & " located at " & Concat(
    Distinct(
        Filter(
            Deliveries,
            Status.Value = "Transport ordered",
            ShowOnPickPackApp = true,
            SPnumber = SP_P01.Text,
            StartsWith(
                yardLocation.Value,
                "P"
            ) || StartsWith(
                yardLocation.Value,
                "T"
            )
        ),
        yardLocation
    ),
    Result.Value,
    " & "
)

If allowing multiple selections feature is required, then StartsWith function will not work on this column.

Best regards,

[AlexTheKidd]

Hi @v-jefferni ,

Oh right - I need to change the yardLocation column to radio selector - used your code, and it worked flawlessy!

One thing I must learn to use: Result.Value, as in my head, it had to be yardLocation.Value. I'm going to go read the documentation again now 🙂

Thanks again, @v-jefferni !!