Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I'm sure this is just a syntax issue but I'm trying to replace a value in a string with another column. I
n this case I want the "99" in "Other (99)" to be the value of another column, "Cycle number." I just don't know how to reference the column in the code.
= Table.ReplaceValue(#"Added Conditional Column2","99","Cycle number",Replacer.ReplaceText,{"What cycle is the group in?"})
This replaces the text with the quoted string but I want the values in the column of the same name.
Thanks!
Thanks
Solved! Go to Solution.
You used the wrong syntax.
each [field name]
not
each "field name"
Look at my code sample above.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUse each [Cycle Name], like below
#"Replaced Value" =
Table.ReplaceValue(
#"Changed Type",
99,
each [Result],
Replacer.ReplaceValue,
{"Value"}
)
Here is full code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYlOlWJ1opSQgyxiIzcC8ZCDL0hJImIO5KVClFkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Value = _t, Result = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Result", Int64.Type}}),
#"Replaced Value" =
Table.ReplaceValue(
#"Changed Type",
99,
each [Result],
Replacer.ReplaceValue,
{"Value"}
)
in
#"Replaced Value"
It replaced the 99 in the highlighted cell with the 7 from the Result column.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUsing each I got this. It replaced the "99" with the column name rather than the value in that column.
= Table.ReplaceValue(#"Added Conditional Column2","99",each "calc-What cycle is the group in?",Replacer.ReplaceText,{"What cycle is the group in?"})
Is there a way to tell it to put the numbers in that column as the replacement string or is this the wrong way to approach the problem?
You used the wrong syntax.
each [field name]
not
each "field name"
Look at my code sample above.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks! I was able to get it to work.
I changed the column name from "calc-What cycle is the group in?" to "cycle" because the column name format was throwing errors when enclosed in brackets.
= Table.ReplaceValue(#"Renamed Columns1","99",each [cycle],Replacer.ReplaceText,{"What cycle is the group in?"})
Yeah, when you have special characters in a field, you have to do it like this, which is a pain:
[#"calc-What cycle is the group in?"]
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting