Replace Value on a filtered column in Power BI

[Anonymous]

I want to replace value from 1 July 2018 to 30 June 2018 which works great but I want to replace only where Trx_Desc = 'Beginning Balance'. I was wondering how to add that to my M code?

let
Source = Sql.Database("abcd", "def"),
relational_COA_Transactions = Source{[Schema="relational",Item="COA_Transactions"]}[Data],
#"Replaced Value" = Table.ReplaceValue(relational_COA_Transactions,#datetime(2018, 7, 1, 0, 0, 0),#datetime(2018, 6, 30, 0, 0, 0),Replacer.ReplaceValue,{"Trx_Date"})
in
#"Replaced Value"

[v-shex-msft]

HI @Anonymous ,

Please provide some dummy data with similar structure as table formula, it is hard to modify your formula without any detail sample data.

How to Get Your Question Answered Quickly 

BTW, why not consider to add a custom column to use if statement to replace record values, it should more simply than coding formula in 'replace value' functions.

Regards,

Xiaoxin Sheng

[Anonymous]

Table is called COA_Transactions. I want to replace values of Date field using m code. I want to replace 1/7/2019 with 6/30/2019 but only where TRX_Desc = Beginning Balance. Rest I want to leave as is.

Sample Data:

Date	COA_Amount	Trx_Desc
1/7/2019	1900	Beginning Balance
1/7/2019	247	Beginning Balance
1/7/2019	1284	Food Expense
1/8/2019	198	Asset Purchase
1/9/2019	198	Asset Purchase
1/10/2019	198	Asset Purchase
1/11/2019	50	Asset Purchase
1/12/2019	256	Asset Purchase
1/13/2019	198	Asset Purchase

[Anonymous]

@v-shex-msft 

[v-shex-msft]

HI @Anonymous ,

You can use the following code to replace your replace value step:

#"Replaced Value" = Table.ReplaceValue(relational_COA_Transactions,each [Trx_Date],each if [Trx_Date]= #date(2019, 1, 7) and [Trx_Desc]="Beginning Balance" then #date(2019, 6, 30) else [Trx_Date],Replacer.ReplaceValue,{"Trx_Date"})

Regards,

Xiaoxin Sheng