Convert a tag list in to columns and sort them

[Anonymous]

Hello to all

I have a field from my query that is something like:

Id  Tags

1   A B

2   A

3   B

4   B C A D

5  C B

6  G

But I need to split the Tags (in this case using space) but I don't know how many can I have (6 would be the higher number), something like:

Id Tag1 Tag2 Tag3  Tag4

1  A       B

2  A

3  B

4  B       C      A     D

5  C       B

I tried split cells, but since I don't know how many I can discard some.

The other problem is that I have a splicer for tag, but know only in TAG.. how can I search I all new columns to filter?

Thanks

[v-yulgu-msft]

Hi @Anonymous,

As you said, we can split the [Tags] into multiple columns based on space. It looks like you have got what you desired. 

Then, what is your concern now? I could not understand "but since I don't know how many I can discard some. The other problem is that I have a splicer for tag, but know only in TAG.. how can I search I all new columns to filter?" Could you please illustrate your problem with more details?

Regards,
Yuliana Gu

[Anonymous]

Hello @v-yulgu-msft

I did not express myself as expected.

In fact I have two situations that I can not solve correctly. I can split the TAG column using the space as delimiter but if I do it on the query editor I have to specify the number of columns but I do not know how many tags do I have. I need it to do it dinamically.

The other situation is that I have a slicer that filters only a set of tags. Using my prior example the slicer as A and B as options, so when splitting the TAG column how I can filter all the columns (A can be in any new column) if I don't know how many do I have.

In this case I would get Id 1, 2 and 4. But the slicer can not filter in column Tag1 and Tag3.

Id Tag1 Tag2 Tag3  Tag4

1  A       B

2  A

3  B

4  B       C      A     D

5  C       B

Can I construct a measure (or calculated column) based on the Slicer option?

Can I order the split?

Thanks

[Ashish_Mathur]

Hi @Anonymous,

After splitting the data into multiple columns, use "Unpivot other columns" to arrange data into 3 columns.  Then you will be able to filter/slice correctly.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Situation 1  

I have to specify the number of columns but I do not know how many tags do I have

There is no need to specify the number of columns, you can split all tags at a time. Please see below images. Here, please duplicate the [Tags] column which will be useful in situation 2. Then, split the duplicated column and rename those new created columns.

Situation 2

Please enter an extra table manually like below to list all available tag options. Later, you need to add this column into slicer.

Create a measure using this formula:

is contain =
IF (
    ISERROR (
        FIND (
            LASTNONBLANK ( 'Select Table'[tag selection], 1 ),
            LASTNONBLANK ( Tag[Tags], 1 )
        )
    ),
    0,
    1
)

Use a table visual to display data. Add above measure into visual level filter, set its to always equal to 1.

Best regards,
Yuliana Gu