Forum Discussion
If Condition without new Column in Power Query
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 |
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.
6 Replies
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
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?
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.
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?
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.
