Slicer to filter multiple columns

[Woodpecker]

Hi all,

Looking to create a slicer that uses all names in the 'Lead' column from below table. I believe the correct format to follow is from the following video, but after splitting the 'Lead' column by delimeter, there are blank values that create difficulty: https://www.youtube.com/watch?v=UvvS376vYSw.

Desired end state is to be able to filter by 'Jane Air' and all prospective clients with her name against them appear.

Any help greatly appreciated!

Cheers 

Prospective Client	Region	Lead	Stage	Currency	Estimated Revenue	Probability (%)	Expected close
XXX	Germany	Joe Bloggs	Pursuit	EUR	91060.5	25-50	Q4 2022
YYY	Germany	Joe Bloggs, John Doe, Jane Air	Win	EUR	25000	50-75	Q1 2022
ZZZ	Germany	John Doe, Jane Air	Loss	EUR	20000	0-25	Q4 2022
CCC	Spain	John Doe	Proposal	EUR	170000	75-100	Q3 2022
QQQ	UK	Joe Bloggs, John Doe	Pursuit	EUR	80000	25-50	Q4 2022
WWW	UK	Joe Bloggs, John Doe, Jane Air	Win	EUR	150000	50-75	Q1 2022
QRR	France	Jane Air, Joe Bloggs	Loss	EUR	140000	0-25	Q4 2022
GGG	France	Jane Air, John Doe	Proposal	EUR	25000	75-100	Q3 2022

[Deoljat · Accepted solution]

Hi @Woodpecker 

If you create a slicer table from the data table removing all columns except the Lead column, then duplicate the Lead column and use split by delimiter 'column to rows' on the Lead column. You will get a table like below that can be linked to your data table and used to filter by all the names.

Also using the Text.Trim function on the Lead column on the slicer table will remove any blank spaces around the names. See M-code below

Slicer Table

Joe Bloggs	Joe Bloggs
Joe Bloggs	Joe Bloggs, John Doe, Jane Air
John Doe	Joe Bloggs, John Doe, Jane Air
Jane Air	Joe Bloggs, John Doe, Jane Air
John Doe	John Doe, Jane Air
Jane Air	John Doe, Jane Air
John Doe	John Doe
Joe Bloggs	Joe Bloggs, John Doe
John Doe	Joe Bloggs, John Doe
Jane Air	Jane Air, Joe Bloggs
Joe Bloggs	Jane Air, Joe Bloggs
Jane Air	Jane Air, John Doe
John Doe	Jane Air, John Doe

The M-code for slicer table (Replace 'SampleData' with your data table name)

let
    Source = SampleData,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Lead"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Duplicates", "Lead", "Lead link"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Lead", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Lead"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Lead", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Lead", Text.Trim, type text}})
in
    #"Trimmed Text"

Hope this helps

[mohammedadnant]

Hi @Woodpecker 

Please see these 2 videos to

Split Columns
https://youtu.be/6nL6cgR5vEU

Columns to rows
https://youtu.be/s7iwN_X82UI

and use this 1 single column as your slicer.

[Woodpecker]

Hi Mohammed - thanks for your response but this doesn't answer my question.  Split by delimeter makes sense; I'm not sure what the 'colums to rows' achieves given that we have multiple text values across a variable number of rows.