Sunday, May 22, 2022
HomeBusiness IntelligenceFast Ideas: Conditionally Substitute Values Primarily based on Different Values in Energy...

Fast Ideas: Conditionally Substitute Values Primarily based on Different Values in Energy Question

Energy Question (M) made quite a lot of knowledge transformation actions a lot simpler and worth substitute is certainly one of them. You may 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 substitute that worth with any desired various. Changing values based mostly on sure circumstances nonetheless, could not appear that simple at first. I’ve seen quite a lot 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 submit I present you a fast and simple strategy to that may aid you dealing with many alternative worth substitute eventualities.

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

Sample Data in Power BI

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

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

Properly, it really works completely tremendous, however wait, you’re including a brand new column proper? Wouldn’t 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 utilize the Question Editor UI to assemble the worth substitute in Energy Question to keep away from misspelling, case sensitivity points, and many others… and truthfully, 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 situation.

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

Power Query Replace Values

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

Replace Values in Power Query

All it’s essential 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 substitute it with a price 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 beneath:

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

So the ultimate code can be:

=Desk.ReplaceValue(Supply, every [A], every [C],Replacer.ReplaceText,{"B"})
Conditionally replace values in Power Query

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 submit right here.



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments