#hashtags

24 Mar, 2021

#hashtags, or getting unique values across multiple table rows

An app that I made for work allows for users to post ideas into a digital suggestion box. One of the features of the entry form is that users can write in their own "special tags" that act as hashtags. An entry could carry multiple tags.

In the app, I have a table view, for which I have a series of filters. One of the filters is for the special tags. I need to select a tag of choice, and all of the entries for that tag need to show.

I had hacked this together in the beginning using some Find() functions and it really didn't work very well. In the end, I revisited it a few times and managed to pull off some table manipulations that looks like it could go blow for blow with Power Query.

Let have a look at it!

The full code

Too long; didn't read. Here it is.

Distinct(
    Sort(
        Filter(
            Split(
                Concat(
                    Filter(
                        
                        table,
                        
                        !IsBlank(Tags)
                    ).Tags,
                    Tags,
                    ";"
                ),
                ";"
            ).Result,
            Len(Result)>0
        ).Result,
        Trim(Result),
        Ascending
    ), Result
)

To analyse this, we need to work inside out. But first, let me show you what this is processing.

Scenario

Let's use a simple example. See this simplified table:

I have a filter that's a dropdown list of unique tag values.

When I select Engineering, I need to see these results (let's not worry about this filtering function for now):

And when I select AI, I need to see this:

Data Storage

I get users to put in their custom tags as a semicolon-separated series of strings, and that's how I store it. It gets sent to a SharePoint column of type "single-line text" and it stays there verbatim.

The main trick is getting the unique values of tags in the first place. Let's start taking a look at the code.

Bring it together

It doesn't help that all of the data values are spread out across multiple rows. If we need to get a single list, we need to bring them in together first.

The first thing I do is filter for the rows that have tags on them. I access the Tags column because that's the only one I'm interested in.

Filter(
    table,
    !IsBlank(Tags)
).Tags

Using our imagination, we should have this as an intermediate result.

The next thing I do is concatenate them all, using a semicolon as separator. (I'll abbreviate previously evaluated expressions as a "...".)

Concat(
    ...,
    Tags,
    ";"
)

Intermediate result:

Engineering; Materials; AI ; Tendering; Engineering; Tendering; Business Development; AI

This already looks like the final result is within reach. If we just turn this into a table, we can filter for distinct values and voila!

Creating a tags table

That's exactly what the Split() function is for.

Split(
    ...,
    ";"
).Result

Notice that the table that results is one with a single column, called Results.

Cleanup

The next two functions are for cleanup. In the real data, we may get two consecutive semicolons, which results in an empty row. We don't want those.

Filter(
    ...,
    Len(Result)>0
).Result,

It would be nice if the values were alphabetical. We also don't want any errant spaces before or after our values. Notice how I stack these functions together, but having Trim() in the sort expression argument of Sort().

Sort(
        ...,
    Trim(Result),
    Ascending
)

Let's check in with our intermediate result. It will have been sorted alphabetically, A-Z.

The final distinct

Finally, use Distinct() on all of this to get our desired result.

Distinct(
    ..., 
    Result
)

We end up with our target result.

That's it!

#powerapps