Energy Question (M) made a number of knowledge transformation actions a lot simpler and worth substitute is one in every of them. You’ll be able to simply proper click on on any desired worth in Energy Question, both in Excel or Energy BI, or different elements of Energy Platform generally, and easily change that worth with any desired various. Changing values primarily based on sure circumstances nonetheless, might not appear that straightforward at first. I’ve seen a number of Energy Question (M) builders including new columns to perform that. However including a brand new column just isn’t all the time a good suggestion, particularly when you are able to do it in a easy single step in Energy Question. On this put up I present you a fast and simple approach to that may enable you to dealing with many various worth substitute situations.

Think about you’ve got a desk like under 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 nice, however wait, you’re including a brand new column proper? Wouldn’t or not it’s higher to deal with the above easy state of affairs 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 less complicated to make use of the UI when attainable than typing lengthy M codes. In any case… Let’s take away the brand new column we created beforehand and undergo the second state of affairs.

Proper click on on a worth in column B and click on “Substitute Values”

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

All you have to do now could be to change the code with the right logic. Let’s evaluation the logic, we need to verify for every worth of column [B] in each single uncooked of the desk and change it with a worth of column [C] provided that [B] = [A].

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

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

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

So the ultimate code could 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 tips on how to reference in Energy Question take a look at my earlier put up right here.