Forum Discussion
Helper IVTrying to change multiple values in one step
Hi everyone
I'm using the following code in an attempt to change multiple values all in one step:
= Table.ReplaceValue(
#"Renamed Columns",
each [Type],
each if [Type] = "157420000" then "A"
else if [Type] = "157420002" then "B"
else "C",
Replacer.ReplaceValue,{"Type"})
The issue I'm facing is that the code works if only one value is present in the Type field, but does not work if more than one comma-separated values are shown in the Type field. We use Dynamics CRM and the Type field is a multi-select option set field, so any ID can have multiple Types. I have shown some images below of what I mean:
ID 16638 has two comma separated types:
ID 44406 has just the one type:
However, when I run the above code, I'm getting 44406 showing as A (correctly) and 16638 showing as C when it should be B.
I've wondered if I should be using Text.Contains or List.Contains, but I just can't seem to get the syntax correct.
Any help would be much appreciated.
Thank you
Jon
jonclay here is what you can do, create a new blank query -> click advanced editor and paste the following M code, call this new query fnGetType (you can use whatever name you want)
(value) => let valueToList = Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)(value), valueToType = Text.Combine(List.Transform(valueToList, each if _ = "157420000" then "A" else if _ = "157420002" then "B" else "C"), "," ) in valueToTypenow add a new column in your table with the following expression
fnGetType([Type])
6 Replies
- parry2k
Super User
jonclay here is what you can do, create a new blank query -> click advanced editor and paste the following M code, call this new query fnGetType (you can use whatever name you want)
(value) => let valueToList = Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)(value), valueToType = Text.Combine(List.Transform(valueToList, each if _ = "157420000" then "A" else if _ = "157420002" then "B" else "C"), "," ) in valueToTypenow add a new column in your table with the following expression
fnGetType([Type])
