Custom Column: Re-using Parameters

[Anonymous]

I am very new to Power BI, so forgive my novice question!

I know how to create a custom column to convert letter values to number values in a separate column, but there are two problems:

1. The number values are entered as text, and I have to manually tweak the code in the advanced editor to remove the quote marks.
2. I want to perform the same create column operation on other columns - the only way I have found to do this is to copy the code in advanced editor and tweaking the column references.

I am sure there is a more efficient way of doing this, but I can't quite work it out. Any suggestions? I have a conversion table already set up in an Excel file, if that helps. Thanks!

[MarcelBeug]

It's hard to understand your situation and requirements.

The numbers may be text, but why are there quotation marks?

What is the source of your data: txt/csv files, current workbook, external workbook, other source?

What do you mean with re-using parameters? I don't see any parameters in your story.

Is the conversion table meant to convert the letters to numbers? Or is it just A=1, B=2 etcetera? Or "1" = 1, "2" = 2, etcetera?

Depending on your exact requirements, maybe something like unpivot might be appropriate, but it is impossible to judge based on the limited information provided,

Please provide further explanation and preferably a link to a file with representative example data.

[Anonymous]

Sorry to appear vague - that's because it's all relatively new to me. Let me try to explain again (I can't share data at the moment, as it is sensitive).

I have an Excel file with data drawn from our pupil database, which I have normalised using Power Query. It consists of text columns:

Name - text

Challange Grade - text (grades from O, E, G, S and U)

Attainment Grade - text (grades from O, E, G, S and U)

I know how to create a custom condtional column to create a new column that converts the challenge grades to numbers (O=4, E=3, G=2, S=1 and U=0), but the code that is generated by the conditional column wizard is still seen as text (because the code generated puts the numbers into inverted commas thus: "4"), so I have to edit this in the advanced query in order for Power BI to treat these values as numbers. That's the first thing.

The second issue is duplicating the custom conditional column to create a conversion column for the attaiment grades: I can either set up another conditional column with exactly the same parameters as the one mentioned above, or I can copy the code in the advanced editor window and then tweak the source column and destination column names. What I am interested in knowing is whether there is a more efficient way of doing this. I wondered about a custom function, but am not sure if it will work.

Does this make any more sense?

I am attaching a very simple representation of what I am after. I hope it helps.

EDIT: How do I attach my sample file?

[MarcelBeug · Accepted solution]

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"

[Anonymous]

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!

[MarcelBeug]

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:

[Anonymous]

Lovely. Thank you very much for your help with this. 🙂