Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

alpha numeric value to integer values in power query

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]

General Comment
Need Help
Show and Tell
Tips and Tricks

3 Replies

  • artemus's avatar
    artemus
    Microsoft Employee
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

       

      Thanks!

  • artemus's avatar
    artemus
    Microsoft Employee
    6 years ago

    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