Energy Question (M) made numerous knowledge transformation actions a lot simpler and worth substitute is one among them. You may simply proper click on on any desired worth in Energy Question, both in Excel or Energy BI, or different parts of Energy Platform usually, and easily exchange that worth with any desired various. Changing values based mostly on sure circumstances nevertheless, could not appear that simple at first. I’ve seen numerous Energy Question (M) builders including new columns to perform that. However including a brand new column isn’t at all times a good suggestion, particularly when you are able to do it in a easy single step in Energy Question. On this publish I present you a fast and straightforward approach to that may show you how to dealing with many various worth substitute situations.

Think about you may have a desk like beneath and you’ve got a requirement to exchange the values column [B] with the values of column [C] if the [A] = [B].

A method is so as to add a brand new conditional column and with the next logic:

if [B] = [A] then [C] else [B]

Effectively, it really works completely effective, however wait, you’re including a brand new column proper? Wouldn’t or not it’s higher to deal with the above easy situation with out including a brand new column? In case your reply is sure then proceed studying.

You should use the Question Editor UI to assemble the worth substitute in Energy Question to keep away from misspelling, case sensitivity points, and so forth… and actually, it’s easier to make use of the UI when doable than typing lengthy M codes. In any case… Let’s take away the brand new column we created beforehand and undergo the second situation.

Proper click on on a price in column B and click on “Exchange Values”

Exchange the chosen worth with any desired worth. In my instance I changed 5 with 1000.

All that you must do now’s to change the code with the proper logic. Let’s assessment the logic, we wish to examine for every worth of column [B] in each single uncooked of the desk and exchange it with a price of column [C] provided that [B] = [A].

I highlighted the “every” because it is a crucial key phrase in Energy Question.

So we solely want to change the Energy Question code as beneath:

• exchange “5” with every [A]
• exchange “1000” with every [C]

So the ultimate code can be:

=Desk.ReplaceValue(Supply, every [A], every [C],Replacer.ReplaceText,{"B"})

The above code finds worth of column [A] in [B], in the event that they’re equal then replaces the worth of column [B] with the worth of column [C].

To learn extra about easy methods to reference in Energy Question take a look at my earlier publish right here.