Replace value on importing of data

[Anonymous]

I have a situation where my source data is dirty. There is a value that should be zero but has a value of non-zero. I would like to replace this with a 0 based on a condition in another field. I think this is rather straightforward but I'm not clear how to do this with the Power BI data query. Also, can this be done inline rather than with a calculated column or a new column?

What is the best way to do this? 

Note: We recognize that we need to fix the source. The process will take longer than trying to gear up the data for the short term. We are fixing the data as part of Warehouse but this is still a needed change that would be important to know.

[nandic]

Hi @Anonymous ,
There are several ways to replace values in Power Query:
1) right click on column, replace values. It is good for replacing fixed values (ie: replace null with 0)
2) add column > conditional column. This way you can make it more dynamic using rules (if then else statement). If you are satisfied with new column, you can remove old column, it will keep the new one.
3) if column is showing error as there are different data types in column, you can right click on column, replace errors

Hope this helps.

Cheers,
Nemanja

[Anonymous]

Hi @nandic 

So if do a new column from the old one, what happens when I reload again? Or is it part of a step 

1) Create a new column

2) Bring over the data in the new column with the condition applied

3) delete old column

Rinse and repeat?

[nandic]

When you refresh data everything will work fine as Power Query saves all the steps which are visible on the right part of the screen.

On the other hand, if you had calculated column outside of power query (created in dax) and you remove it, all columns that were using that column will return error.

[parry2k]

@Anonymous check this post on how to conditionally replace the value in power query

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

I think I'm not thinking about this correctly. Let me rephrase this.

In two columns, one is called Broker the other is called Security Type we have a value in Amount that has 1000.

How can I make a conditional column that looks at two columns (sort of a WHERE clause that has Security Type and Broker criteria) that sets the amount = 0.

The examples I saw do a replacer but the conditional column doesn't have multiple conditions on it.

@parry2k  @nandic  Any thoughts?

[nandic]

@Anonymous , conditional column can have multiple conditions, looking at different columns.
Here is example where i randomly used 3 columns and based on criteria returned value from specific column or entered value.
* example is random, not real logic 🙂

[Anonymous]

I think it would be best to show you:

These two fields:

Sec_typ_name = either Currency Forward or Currency

and bkr_name = Credit Suisse - High Touch

and if the Sec_name = 

then this column should be 0

[parry2k]

@Anonymous also this link can be helpful.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡