Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Woodpecker
New Member

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 ClientRegionLeadStageCurrencyEstimated Revenue Probability (%)Expected close
XXXGermanyJoe BloggsPursuitEUR91060.525-50Q4 2022
YYYGermanyJoe Bloggs, John Doe, Jane AirWinEUR2500050-75Q1 2022
ZZZGermanyJohn Doe, Jane AirLossEUR200000-25Q4 2022
CCCSpainJohn DoeProposalEUR17000075-100Q3 2022
QQQUKJoe Bloggs, John DoePursuitEUR8000025-50Q4 2022
WWWUKJoe Bloggs, John Doe, Jane AirWinEUR15000050-75Q1 2022
QRRFranceJane Air, Joe BloggsLossEUR1400000-25Q4 2022
GGGFranceJane Air, John DoeProposalEUR2500075-100Q3 2022
1 ACCEPTED SOLUTION
Deoljat
Regular Visitor

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 BloggsJoe Bloggs
Joe BloggsJoe Bloggs, John Doe, Jane Air
John DoeJoe Bloggs, John Doe, Jane Air
Jane AirJoe Bloggs, John Doe, Jane Air
John DoeJohn Doe, Jane Air
Jane AirJohn Doe, Jane Air
John DoeJohn Doe
Joe BloggsJoe Bloggs, John Doe
John DoeJoe Bloggs, John Doe
Jane AirJane Air, Joe Bloggs
Joe BloggsJane Air, Joe Bloggs
Jane AirJane Air, John Doe
John DoeJane 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

 

View solution in original post

3 REPLIES 3
Deoljat
Regular Visitor

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 BloggsJoe Bloggs
Joe BloggsJoe Bloggs, John Doe, Jane Air
John DoeJoe Bloggs, John Doe, Jane Air
Jane AirJoe Bloggs, John Doe, Jane Air
John DoeJohn Doe, Jane Air
Jane AirJohn Doe, Jane Air
John DoeJohn Doe
Joe BloggsJoe Bloggs, John Doe
John DoeJoe Bloggs, John Doe
Jane AirJane Air, Joe Bloggs
Joe BloggsJane Air, Joe Bloggs
Jane AirJane Air, John Doe
John DoeJane 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
Impactful Individual
Impactful Individual

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.

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors