Hi All,
How can the following be done? Replacing one value in a column( Instead of using Replace values multiple times) with different values with a condition on a different column in Power BI
This should now show as
Basically,
If Course = 105 or 106, then it should replace the subject as English 1(105) and English 2(106)
You would then need another step afterwards to remove the original column though. However, if you end up with more than 2 replacements needed, this way will still remain as 2 steps. 1 for the change, 1 for the hiding.
Let me state that the solution by @jamie.bryan is the most straight forward one, when considering the value in the Course column.
Iāve made some assumptions of my own about your data and table.
The table is a dimension table, each Course is unique and listed once
The data is already sorted in the order you require
When that is the case, counting repeated items should get you the expected result. Therefore copy this script into a new blank query and see if that meets your requirement.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRcs1Lz8kszlCK1QGJGAFFfBNLYFxjIDc4OTM1LzkVKmICFHEsKoHyTDEMMMMQMQeKBLgqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Course = _t, Subject = _t]),
Buffer = Table.Buffer( Source ),
ReplaceValue = Table.ReplaceValue(Buffer,each [Subject], each
let c = Table.RowCount( Table.SelectRows( Table.FirstN( Buffer, List.PositionOf(Buffer[Course], [Course] )+1 ), (x)=> x[Subject] = [Subject] )) in
if c=1 then [Subject] else [Subject] & "-" & Number.ToText(c-1),Replacer.ReplaceText,{"Subject"})
in
ReplaceValue
Hi @Vsb79 @jamie.bryan posted a simple solution for your task. You didnāt accept Jamieās solution becaues it has more than one step.
Here is your one step solution with the same logic:
let
Source = Excel.CurrentWorkbook(){[Name="Subjects"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,each [Subject],each if [Course]=105 then "English-1" else if [Course]=106 then "English-2" else [Subject],Replacer.ReplaceValue,{"Subject"})
in
#"Replaced Value"
One point of consideration for future development of this report - be kind to your future self.
You donāt want to have to minutely examine EVERY LINE in the advanced editor to figure out what you did because it seemed easier to combine into a single line of code.
I have run into situations like this in reports that I developed, and others that I am now responsible for that were developed by someone else - and believe me, 6 months or a year from now, debugging reports with too much creativity in the M code can be frustrating if they are not well documented.
Sometimes creative solutions are needed, and sometimes they are just fun to develop - but consider documenting them.
Example:
let
Source = Excel.CurrentWorkbook(){[Name="Subjects"]}[Content],
//the following line fixes the subject for English courses 105 and 106 only
#"Replaced Value" = Table.ReplaceValue(Source,each [Subject],each if [Course]=105 then "English-1" else if [Course]=106 then "English-2" else [Subject],Replacer.ReplaceValue,{"Subject"})
in
#"Replaced Value"
@Heather the need and argument for documentation is not related to the creativity of the solution.
None of the suggested solutions would answer or explain the logic behind the replacement.
=> all would equally benefit from a comment line documenting the reasoning
It is also not related to the number of lines you spend on them.
In this case the one-liner is easier to understand and document then a multi line solution.
=> a name change from āReplaced Valueā to āEnglisch 105/106ā might be helpful for this step
Agreed, in this specific case, however it is often true in forum solutions that we are only seeing a very simplified version of the true problem. If a user were to expand your solution to cover 10-15 text replacements (which yes, I have seen happen), then an explanation of what is going on would be helpful for future development of that same report.
Also, because there is not a simple way to āseeā what is being replaced using the basic UI, a future developer (who may not be as creative with their M code) could benefit from a helpful comment such as I suggested - or the one you recommended. The reason I call out the ācreativity of the solutionā is simply because of the lack of UI guidance in creating something like this.
In any case, the issue is solved, and I have enjoyed this discussion.