Forum Discussion
Solved
SWITCH with wildcards within the value
Hi, I have a list of values, in this case internal IP addresses, that I am trying to relate to a physicall location. The list of IPs I have are all exact IP addresses and I am trying to get the g...
1. In DAX you can do it like this:
Column = SWITCH( LEFT('Table'[IP Addresses:],SEARCH(".",'Table'[IP Addresses:],SEARCH(".",'Table'[IP Addresses:])+1)), "192.13.", "NYC", "168.192.", "Boston" )2. With Power Query you get the following solution:
// Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ00jM01jMx1TMyVYrVQQgY6RlYIAsYWuoZmUEEzCz0QIImesamKAJGJkCFSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"IP Addresses:" = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.BeforeDelimiter([#"IP Addresses:"], ".", 1) = "192.13" then "NYC" else "Boston", type text) in #"Added Custom"With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
FrankAT
Community Champion
Community Champion1. In DAX you can do it like this:
Column =
SWITCH(
LEFT('Table'[IP Addresses:],SEARCH(".",'Table'[IP Addresses:],SEARCH(".",'Table'[IP Addresses:])+1)),
"192.13.", "NYC",
"168.192.", "Boston"
)
2. With Power Query you get the following solution:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ00jM01jMx1TMyVYrVQQgY6RlYIAsYWuoZmUEEzCz0QIImesamKAJGJkCFSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"IP Addresses:" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.BeforeDelimiter([#"IP Addresses:"], ".", 1) = "192.13" then "NYC" else "Boston", type text)
in
#"Added Custom"
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)