Power BI Search Text Parts in Text
Problem Statement: We have been given a text column, that has 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 slicer on A, B, C, D
Solution: We do not want to split the column: Name. Because values may duplicate, so we decided to create a new table of Names, with names and text parts.
- Created a table, two columns having data of name column, 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). By default, it will be 1-M join from Data to Names. Change that to many to many, filter direction Name to Data.
Now you can use the Key from Name Table as a filter and create the 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 attached file. Please like, share, and comment.
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
