Forum Discussion

newgirl's avatar
newgirl
Post Patron
5 years ago
Solved

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 ...
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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.

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
    newgirl
    Post Patron
    5 years ago

    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?