Conditional replace value - No syntax error

[wcoetsee]

Hi all,

I am trying to replace the value in [OriginalPathkey.4] with the number 805 if the value in [AssetType] is equal to CASHADJ but it is not working. I have tried changing the Replacer.ReplaceValue with Replacer.ReplaceText but with no success.

I have noticed that the data type of [OriginalPathkey.4] changes when applying this step, where the data type was whole number in the previous step. Thank you for your assistance.

[ronrsnfld]

Here's one way to transform a column based on another column: (obviously using a made-up table)

Original

let
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OriginalPathkey.4", Int64.Type}, {"AssetType", type text}}),
    xformPathKey= Table.FromRecords(
            Table.TransformRows(#"Changed Type", (r) => 
                Record.TransformFields(r,{"OriginalPathkey.4", each if r[AssetType]="CASHADJ" then 805 else _})))
in
    xformPathKey

Result

[PijushRoy]

Hi @wcoetsee 

If problem is not solved, please share some sample data 

[Anonymous · Accepted solution]

It would be easier to make a custom column like:

Table.AddColumn(#"Changed Type1", "NewValue" each if [AssetType] = "CASHADJ" then 805 else [OriginalPathKey.4], Int64.Type)

--Nate