Are you able to please suggest a solution for an alphanumeric string that I need to clean up and convert to number.
It's a banking record (attached sample file). It has a "C" at the end to represent a credit. For instance, 800.00C is meant to be -800.00 on the bank statement. (900.00 is as stated (i.e., debit 900.00).
So in this instance, I would like to keep the 900.00 as is and convert all numbers that have a C at the end to a negative number. Thanks for any assistance you can provide.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DATA", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([DATA],"C") then Text.Start([DATA],Text.Length([DATA])-1) else [DATA]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if Text.Contains([DATA],"C") then [Custom]*-1 else [Custom]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
#"Removed Columns"
Thank you very much for this. I really appreciate your help. I'm going to try it out on my template.
Here is my attempt at this. I just replaced "C" with "-" and converted column to decimals. I guess you could eliminate duplicating the column and do this on the source column alone. Alan is definitely more experienced than I am. I was just thinking of another way to try this.
let
Source = Excel.CurrentWorkbook(){[Name="BankData"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DATA", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "DATA", "DATA - Copy"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","C","-",Replacer.ReplaceText,{"DATA - Copy"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"DATA - Copy", type number}})
in
#"Changed Type1"
