July 17, 2018
Hi I get a results spreadsheet emailed from a lab, but some of the results are in an unconventional format 5.50x10^3, I have written some code that will work on one column but it involves splitting columns making 4 columns and then doing the calc, but the potentail is for multiple columns is there a way to loop this with out having huge mmass of code?
#"Replaced Value3" = Table.ReplaceValue(#"Added Custom","< ","",Replacer.ReplaceText,{"Result"}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Replaced Value3", "Result", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Result.1", "Result.2", "Result.3", "Result.4"}),
#"Replaced Value4" = Table.ReplaceValue(#"Split Column by Character Transition",null,"0",Replacer.ReplaceValue,{"Result.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value4",{{"Result.1", type number}, {"Result.2", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each [Result.1]+[Result.2]),
#"Replaced Value5" = Table.ReplaceValue(#"Added Custom1","^","",Replacer.ReplaceText,{"Result.4"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","x","",Replacer.ReplaceText,{"Result.3"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value6",{"Result.1", "Result.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Result.3", Int64.Type}, {"Result.4", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "Result", each if [Result.3]= null then [Custom] else [Custom]*Number.Power([Result.3],[Result.4])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Result.3", "Result.4", "Custom"}),
1 Guest(s)