The ever-awesome PowerPivotPro gurus posted a thread on how to do a CONTAINS search from a measure selection. The list to use in the slicer is generated by a query as shown in a simplified sample below.
The problem is that PowerQuery appears to eliminate spaces from the list in this query leading to the equivalent of wildcard searches. As an example at our research facility we use mice and rats and I would like to pull all values where "rat" is present in a description, but not "aspirator" or "rating". Is there a parameter I can set where PowerQuery will preserve the spaces?
let
SourceList = {"Angiocath",
"Catheter",
"Electrode",
"Rat ",
"Stent",
"Suture",
"Syringe",
"Wire"},
StartList = Table.FromList(SourceList,Splitter.SplitByNothing(),{"Keyword"}),
ChangeToText = Table.TransformColumnTypes(StartList,{{"Keyword", type text}}),
AddIndex = Table.AddIndexColumn(ChangeToText, "Keyword Index", 1, 1),
ChangeToInteger = Table.TransformColumnTypes(AddIndex,{{"Keyword Index", Int64.Type}})
in
ChangeToInteger
The problem is that PowerQuery appears to eliminate spaces from the list in this query leading to the equivalent of wildcard searches. As an example at our research facility we use mice and rats and I would like to pull all values where "rat" is present in a description, but not "aspirator" or "rating". Is there a parameter I can set where PowerQuery will preserve the spaces?
let
SourceList = {"Angiocath",
"Catheter",
"Electrode",
"Rat ",
"Stent",
"Suture",
"Syringe",
"Wire"},
StartList = Table.FromList(SourceList,Splitter.SplitByNothing(),{"Keyword"}),
ChangeToText = Table.TransformColumnTypes(StartList,{{"Keyword", type text}}),
AddIndex = Table.AddIndexColumn(ChangeToText, "Keyword Index", 1, 1),
ChangeToInteger = Table.TransformColumnTypes(AddIndex,{{"Keyword Index", Int64.Type}})
in
ChangeToInteger
