SWITCH with wildcards within the value

[keithkeirstead]

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 general location.

IP Addresses:

192.13.45.25

192.13.42.08

192.13.19.26

168.192.4.35

168.192.24.13

What I was attempting was to use the SWITCH to change 192.13.* addresses to NYC, while 168.192.* to Boston, ect. for ~500 subnets. However I couldn't see to get any wildcards to work within the IP address, is there anyway to do that? This is what I had:

Location = SWITCH(releaseip[Column1], "192.13.", "NYC", "168.192", "Boston")

[datafan]

Nicely done!!

[CNENFRNL]

Hello, @keithkeirstead, you may want to test this type of measurement or expand the location options with more subnet masks in line with your actual dataset,

Location = 
SWITCH (
    FALSE (),
    ISERROR ( SEARCH ( "192.13.", MAX ( ReleaseIP[IP Address] ) ) ), "NYC",
    ISERROR ( SEARCH ( "168.192.", MAX ( ReleaseIP[IP Address] ) ) ), "BOSTON",
    "Other City"
)

[FrankAT · Accepted solution]

Hi @keithkeirstead 

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)