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 a...
General Comment
Need Help
Show and Tell
Tips and Tricks
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