Forum Discussion

wcoetsee's avatar
wcoetsee
New Member
4 years ago
Solved

Conditional replace value - No syntax error

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.Replac...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

ronrsnfld's avatar
ronrsnfld
Super User
4 years ago

 

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