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 ...
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"
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
