Forum Discussion
Slicer to filter multiple columns
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 |
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
3 Replies
Hi Woodpecker
Please see these 2 videos to
Split Columns
https://youtu.be/6nL6cgR5vEUColumns to rows
https://youtu.be/s7iwN_X82UIand use this 1 single column as your slicer.
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.
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
