If Condition without new Column in Power Query

[newgirl]

Hi! 

I have a project that has a sample data table below:

Client	Date	Volume	Type	Comment
Alpha	1/1/2021	600	DR	Price up
Beta	1/5/2021	400	ZB4
Alpha	5/2/2021	100	CR	Price Down
Alpha	5/2/2021	500	ZB4

As you can see, under "Comment" column, if the type of transaction is classified as 'ZB4', the comment is blank.

What I want is that if the value under 'Comment' column is blank and that the value of the row is 'ZB4' under 'Type' column, the value under the 'Comment' column would be filled out as "Rebates".

I know I can do this by using additional conditional column but I wanted to know if it's possible to fill out my required value without adding a new column in Power Query.

Expected output:

Client	Date	Volume	Type	Comment
Alpha	1/1/2021	600	DR	Price up
Beta	1/5/2021	400	ZB4	Rebates
Alpha	5/2/2021	100	CR	Price Down
Alpha	5/2/2021	500	ZB4	Rebates

[v-robertq-msft · Accepted solution]

Hi, @newgirl 

According to your description and sample picture, it seems that the reason for this problem is that these two rows of data aren’t truly blank values in the Power query, they may be like “ “ or “  ” with spaces and will not be affected by the formula. Therefore, I suggest you to use the “Replace value” function in the Power query to fill these two blank values.

You can enter spaces in the “Value To Find”:

If this method is not possible to replace the blank values, you can just go to the Power BI to use a calculated column to get your expected column because formulas in Power BI can’t be affected by spaces in the value:

Column =

IF('Table'[Order Reason1]=BLANK()&&[Sales Document Type]="ZB4","Rebates",[Order Reason1])

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ERD]

Hi @newgirl ,

You can add a step and a code:

= Table.ReplaceValue(PreviousStep, each [Comment], each if [Type] = "ZB4" and [Comment] = "" then "Rebates" else [Comment],Replacer.ReplaceValue,{"Comment"})

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[newgirl]

Hi  ! THank you so much for this, it helped a lot.

This is my version in my official file: ("Order Reason1" is the equivalent of "Comment" while "Sales Document Type" is the equivalent of "Type")

#"Replace Value" = Table.ReplaceValue(#"Changed Type", each [Order Reason1], each if [Sales Document Type] = "ZB4" and [Order Reason1] ="" then "Rebates" else [Order Reason1], Replacer.ReplaceValue, {"Order Reason1"})

After I clicked Close and Applied, the query has loaded the changes. So at first glance, it can be said that the formula worked.

After I filter the column for ZB4 only, and then check the remaining values under "Order Reason1" column, it shows there are 2 values - (blank) and "Rebates". I filter for (blank), since my expectation is that there shouldn't be anymore blank.

Surprisingly, there were 2 remaining rows that seem unaffected by the formula.

I wanted to ask if maybe you would have an idea why these 2 rows were unaffected, considering their "Type" is "ZB4" and their "Order Reason1" is blank, which should fall under the new formula applied. Maybe there should be additional conditions?

[ERD]

@newgirl , you need to check the values. If your "Order Reason1" is really blank (no spaces, hidden symbols), same for the "Type".

[amitchandak]

@newgirl , In power query right click on the column, and try to replace null  with Rebates

In the replace box you will get the option of what string you want to replace.

null should take the blank

[newgirl]

Hi @amitchandak ! In the replace box, is it possible to inpu a formula in which only for rows with null 'Comment' values and 'ZB4' Type should be replaced with "Rebates" under 'Comment'.

I've used 'Replace Values' function before, but I didn't know it can accept a formula?