Replace values in one column with multiple conditions in another column in a single step using M

Power Query
1

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
image

This should now show as
image
Basically,
If Course = 105 or 106, then it should replace the subject as English 1(105) and English 2(106)

Thank you

2

Hi @Vsb79

You could add a conditional column like so:

image
image937Ɨ433 12.1 KB

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.

1 Like
3

Thanks Jamie, for the solution. But I am trying to see if this can be achieved in one step using M code.

4

@Vsb79 What is the logic for 105 and 106? Why did you not consider 101?

5

Hi @Vsb79,

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

.
With this result
image

1 Like
6

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"

Regards,
Matthias

7

Thanks Matthias, I did try it and it worked.

1 Like
8

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

Just something to think about

2 Likes
9

@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

2 Likes
10

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.

2 Likes