alpha numeric value to integer values in power query

[Anonymous]

Hello,

I am trying to utilize the following code in a parameter. The code is already working and provides the number 1. What I am trying to do is to provide the user the ability to select Hawaii and many other states and have in the back end utilize the number.
In this example Hawaii is assigned as a 3. Currently, if I delete the bolded part it does work it does provide the number 1 which is great, but I only know what state 1 is.

Any help is much appreciated.

90 meta [IsParameterQuery=true, List={ Value.Type("Hawaii" = List.ReplaceValue(list,"Hawaii",3,Replacer.ReplaceText)),1}, DefaultValue=..., Type="Any", IsParameterQueryRequired=true]

[artemus]

Here is an example

// ReplaceMeWithAMappingTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZVhb9sgEIb/StTPU/4Ds2lMbUMEJK437QNLWIPimso4bbNfP+7ASSdFyb3H4wMfL+TnzwfSkO+kJQ/fHshgfptXA1GT5Prh1zckVJ2BcMbx+q6QkOyH4IhM7q8fkfmRZS4ia8IVUZg8mzGYAKFMOjEFadijkJxBpcIM7o+fRgfFilsmk6IRkpQCsn7wkzl6CBe9QJzTQrNipzE/jvYwu8NlBrWkElrShnRE0pgs7WA+zGQhzDoxj3FKVsJCHuOM7gjremxQ5kZtqJAbXPzG+ukFV75BmYEqTsJYTFRxCucg+KKAiBNU8BbsaE7wSqy8CRxuGsYFgy6yYXCjd9BF1iSdJmG8ZAQ3g41HZ3AzGE8yE6LDYf+BY7cYxm67VC97VGeVnq0p17ui7iFnx/lyOF8hRH1NSCN2TOUlNP7iQl5Eg4ncipYwDu1tjRuh1+1dpGHZN4SXmJyugxmPEKI+LhWUIkW1U1RrhVwI5nC6BDvPsG70NCQzzoqKbQg0onWHk3sxI4Qs6wWKllFC46MuGib4GZbe8pxYMKXgs92mx0OAz9sb7GircuqOip1cOH+ZEII9brNPW8F16lbrxzn1qtVZJoTT7zIfQm5/T/kYYgOjXpg9KRPxbtCdfJ/UMt6tKtJuVcVkevBjVZnXt3By6HZewde6utNPVCraZ/TJTsHCXvMn5J7uXEufGZ494Fr76Q5gXd4i1965Xsg6U72fzhBi9XWfGSF1tSqIFNHl+CZ+mk+rwkw+eh3fqEC++MqXpE4blujSnNOeoXn4ukysqBisUJwcrE1US4xjdRNPHfpFnId41vASFHXWuYCkGwE2EJN98eAdIZNK41vKueqbPUnX19aOY7gO7yZdYFvM5VKyEiVdMZUNLk/+aFcsZJOjV+SaJTaa5/+eKH/5vycKeqKWniT+1pNE33qiSmRzTzQFtysKbtAW3B4sOEHznFiwZ7wRtP3EC0E/3wQM7zSBbu5mc4IfvcQwtqcS3B1Tezu9RjtDhDIbe8/ijZk6tnfxykzd2qPO3epIdCzfaGx+Z6Jdx5cZNwBvMchkkCq9+lKws2FefanawXHo1re6TMV/CIVXY+fCwY/BYVWWEpnqRbwUNpC6+tc4NUR9kpH49Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [NameInCaps = _t, Name = _t, Abr = _t, Abr2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

// StateName
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZLbbsIwDIZfpeJ6LzGBNjoGTDuhCXFhWtNYTW0Up0D39AtN2rFJvfg/x/HhT7fbyb2FPTQw2d31WuskHX0LD7oGVtAepmDpII4pnk3FioNSEjBj4alofc8ztHAGhz08hEQq461HFFelCvOQQtTLvAQTK+XWEgtpziVBGiOXcxSL32kWyL4t6q6HZ2lJx/QlEGNSrrPAZQJVKEyr6H2ssaTCUAWcIKyg4lMNUr1+xyONLK1LIOxDsxXu3WjbCk9QDvKczaE5qqHkwDXyhE6xG3GJFypkxC9xdQRx3mRTcBJ8gJvQDOphuLWheHNd2+BbesO1w0riLi/IrJ09QXisVyMlZrmOPrxJ+79DDN10eMerGYqY6JJc//BgevGJrgkuRE3hSYffYgNhba58mmSD6rO/CaSFsFI676QJ6ZPd7gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    Name1 = #"Added Index"[Name]
in
    Name1

// State
"Washington" meta [IsParameterQuery=true, ExpressionIdentifier=StateName, Type="Text", IsParameterQueryRequired=true]

// ReplaceMeWithYourDBData
let
    Source = {1 .. 50},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Index"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each List.Transform({1 .. 10}, each Number.Random())),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "MyData"}})
in
    #"Renamed Columns1"

// YourQuery
let
    Source = ReplaceMeWithYourDBData,
    #"Merged Queries" = Table.NestedJoin(Source, {"Index"}, ReplaceMeWithAMappingTable, {"Index"}, "ReplaceMeWithAMappingTable", JoinKind.LeftOuter),
    #"Expanded ReplaceMeWithAMappingTable" = Table.ExpandTableColumn(#"Merged Queries", "ReplaceMeWithAMappingTable", {"Name"}, {"Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded ReplaceMeWithAMappingTable", each [Name] = State),
    MoreStepsHere = Table.AddColumn(#"Filtered Rows", "Addition", each [MyData] + 33, type number)
in
    MoreStepsHere

[artemus]

Don't try to do this inline. Make, or get a table with the state to id mapping. Then in your parameters choose the column with the state name as the options for the user. In your code, Make a new query by selecting reference of the mapping table. Then filter it by the query parameter. Finally, use the table in a semi join on your dataset.

[Anonymous]

Thank you so much for your responce - by any chance you have an example? 

Thanks!