Issue with Slicer while combining two columns

[Anonymous]

Hello all,

I am new to PowerBI and am trying to create a view in PowerBI with the help of slicer.

I have a table which has 3 columns namely Title, Primary Author, Secondary Author. I am trying to create a slicer which will list all the  distinct names from both primary author and secondary author columns and when I click/select a name, the table should list all the titles wherever the name is available as either primary author or one of the seconday authors.. 

For example: 

Title - Primary Author - Secondary Author

ABC - John - Dev, Daisy, Mark

DEF - Dev - Pat, John, Tony

If I select John in slicer, it should give me both ABC and DEF in PowerBI dashboard..

Can someone please help me to solve?

[mlsx4]

Hi @Anonymous 

Don't know if you need the word "Dev", but anyway, what you have to do is to use Power Query here.

This is your original table. So I'm going to split both column by delimiter. First I select Primary author and use "-" as delimiter.

I have selected the most left one, but because you only have one. If you have several "-" choose each time of appearance. (I will assume you need Dev, if not, filter later on) 

Do the same step for Secondary Author but choose "," as delimiter.

You will get something like this.

And finally, unpivot columns (you select the columns with names of authors and go to Transform-> Unpivot:

And here it is:

If you don't need Dev you can filter at this point:

And now, you only need to put a slicer

[Anonymous]

Many Thanks for your guidance!!

I am facing an issue with the last step. When I try to select the columns and unpivot selected columns, it's creating two columns namely "Attribute" and "Values" and "Error" is filled in all the rows.. 

I tried to unpivot by selecting a)only the primary and secondary authors columns b) selecting title and authors columns. both are not working. 

could you please shed some light on what am I missing?

[mlsx4]

Ok @Anonymous . I got "Attribute" column too but I removed it (I forgot to mention this last step, sorry about that)

Have you checked that all the values are text? To unpivot just select option a), never the title

I assume you have reached this point without error, isn't it?

[Anonymous]

No luck!! 😞 Will it not work if there are rows with "null" values?? 

I am able to split the columns based on delimiter but unable to unpivot and get the desired output...

[mlsx4]

Can you share an example of how is your data before unpivot? Paste here some table with the values you have please. (change names by "AAA" or something like this to keep your data confidential). Because it is weird. Null values doesn't affect in this case

[Anonymous]

@mlsx4  please find below the image before unpivot for your reference....

[mlsx4]

Hi @Anonymous 
And at this point, if you select the four columns and click on unpivot it gives you error for all rows?

[Anonymous]

@mlsx4  - Sorry for the delayed response. 
But yeah, am getting error if I select those 4 columns and unpivot it.