Power BI Search Text Parts in Text

Problem Statement: We have been given a text column with some comma-separated values. A slicer on each such text part should be able to filter the table. In the data given below, we would like to have a slicer on A, B, C, D

Solution: We do not want to split the column: Name. Because values may be duplicated, we decided to create a new table of Names, with names and text parts.

1. Created a new table, with data in the name column duplicated in two columns, in Power Query

2. Then used split by a delimiter to get the slicer key

3. Remove duplicates. The complete Power Query script is

let
    Source = Table.FromColumns({Data[Name],Data[Name]},{"Name", "Key"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Key", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Key"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Key", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type")
in
    #"Removed Duplicates"

4. Go to Data model view. Join both tables (Data and Name). From Data to Names, it will be a 1-M join by default. Change relationship to many to many, filter direction Name to Data.

Now you can use the Key from Name Table as a filter and create a visual with the sum of Value (In Table Data).

If needed create a measure

Total Value = sumx(SUMMARIZE(Data, Data[Name], Data[Value]), Data[Value])

In my example, this measure will not make a difference. But may be needed in a complex data model.

This is how it will work, finally.

You can find the files here. Please like, share, and comment.

You can find the video here

Find all my Medium blogs here

Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.

You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share

Master Power BI | Expertise Power BI | Power BI For Tableau Users | DAX for SQL Users