Forum Discussion
Solved
Help with customised Slicer
Hi, I am creating a PBI report for my work. There is a column that has multiple text rows. But I need a slicer that selects one of the data points, not the entire column. E.g., in the below table, I have solutions that are not unique. Now I need a filter that gives me unique value, e.g. The filter option shows individual values, e.g., A, B C D E F etc.
Thank you so much for your support in advance.
Create a mapping table that links each solution to the individual items.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyVYrViVZy0nEGcoyNoBwXHVcQ1xjMddVxAymEcBx1oEpNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Solution = _t, cost = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Solution", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Solution"), #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter", {"Solution"}), #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"cost"}) in #"Removed Columns"let Source = Solutions, #"Added Custom" = Table.AddColumn(Source, "Custom", (k)=> Table.SelectRows(Facts, each List.Contains(Text.Split([Solution],","),k[Solution]))), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Solution"}, {"Match"}) in #"Expanded Custom"
2 Replies
Create a mapping table that links each solution to the individual items.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyVYrViVZy0nEGcoyNoBwXHVcQ1xjMddVxAymEcBx1oEpNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Solution = _t, cost = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Solution", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Solution"), #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter", {"Solution"}), #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"cost"}) in #"Removed Columns"let Source = Solutions, #"Added Custom" = Table.AddColumn(Source, "Custom", (k)=> Table.SelectRows(Facts, each List.Contains(Text.Split([Solution],","),k[Solution]))), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Solution"}, {"Match"}) in #"Expanded Custom"Thank you for your reply. Highly appreciated. Let me try the same and get back to you. For time being, i used Text filter as a work around
