Power Query to filter

[Anonymous]

Hello Community,

My data source is in Excel and “Price Check” column data type is decimal in power bi.

I want to filter “Not Valid”=0 or Blank in Power Query.I have tried TEXT.CONTAINTS but not able to get correct result due to data type.

Can any one has any suggestion?

ROW DATA

Price Check

122.63

25.96

Not Valid

36.88

77.96

FINAL RESULT

Price Check

122.63

25.96

0 or Blank

36.88

77.96

[Anonymous]

Hi,

What you can do is, add a new column

new column = IF(  OR( [price Check] = 0 , [price Check] = BLANK() ), 0, 1)

So in this new column, it gives 0 to 0 and blank, and gives 1 to the rest. You can filter this column instead of the original one. 

[Anonymous]

Hi Tatasherrry,

Thank you so much for your suggestion.

My table has multiple column and "Price Check" is one of the column,In calculation i am also using other column so it is not possbile for me to remove the row with "Not Valid" comments.

let me know if you need more information.

[Anonymous]

Okay, I understand this time.

What you need to do is quite easy:

Go to "Edit queries", right click the column header, choose "replace", replace "Not Valid" to 0 or leave it blank.

[Anonymous]

Column data type is decimal and power bi not accepting value to find as a text.I have tried to change the data type to text and
use the replace value but not working .

replace

[Anonymous]

When you try to change the data type but it shows you an error,

it means you have to go to "Edit Queries", then "transform", then "data type : text".

Let me know if it is works 🙂

[Anonymous]

Hi Tatasherry,

Thank so much for your suggestion.Still i am getting an error.I found the other workaround and it is working fine for me.

Thanks

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

If there is an error in your original example, please replace error with 0 or blank.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.