Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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]

3 REPLIES 3
artemus
Employee
Employee

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
Employee
Employee

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
Not applicable

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

 

Thanks!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors