cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Convert a tag list in to columns and sort them

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

1 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.

1.PNG2.PNG3.PNG

 

 

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.

4.PNG

 

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.

5.PNG

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

1.PNG2.PNG3.PNG

 

 

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.

4.PNG

 

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.

5.PNG

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors