Forum Discussion
Custom Column: Re-using Parameters
Thanks for clarification. You can put a sample file somewhere on internet (e.g. OneDrive) and provide the link.
Anyhow, I would add custom columns and use function Text.PositionOf to convert the text to numbers.
As a final step, you can change the column types to numbers.
let Source = Table1, #"Added Custom" = Table.AddColumn(Source, "Challenge Score", each Text.PositionOf("USGEO",[Challenge Grade])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Attainment Score", each Text.PositionOf("USGEO",[Attainment Grade])), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Challenge Score", Int64.Type}, {"Attainment Score", Int64.Type}}) in #"Changed Type"
Thank you very much. I am afraid I am not yet savvy enough to understand exactly what that code is doing, but I'll give it a go. What does Text.PositionOf do exactly? And Int64.Type? I like to understand how the individual elements work so that I can then attempt to adapt and reuse them later on. Thanks again for your help!
Text.PositionOf returns the position (zero based) of the character in the second parameter, in the text in the first parameter, so
Text.PositionOf("USGEO","U") = 0
Text.PositionOf("USGEO","S") = 1, etcetera
Int64.Type is the result of changing the type of a column to whole number: select both column (Ctrl-click) and adjust the data type e.g. on the transform tab:
Lovely. Thank you very much for your help with this. :)