Forum Discussion

Data4920's avatar
Data4920
Frequent Visitor
6 years ago
Solved

List location based on IP address from a range in another table

I'm trying to match up a location to a specific asset based on the assets IP address given a list of ranges from another table.  Neither table has a unique column that will allow me to join them toge...
  • lbendlin's avatar
    lbendlin
    6 years ago

    Not sure if I am oversimplifying this but here's a version. Calculated Column in the IPs table.  No relationship between tables.

     

     

     

    NetworkID = 
    var n = IPs[IPNum]
    return CALCULATE(max(Networks[Network ID]),Networks[IPStartNum]<=n,Networks[IPEndNum]>=n)

     

     

     

    Here are the definitions for the queries I used:

     

     

     

    Networks:
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYy7CoAwDEV/RTqXcJs07ew3OEpxcnD3/zF9gAURMpxDuGff3Xbd57I6P+AIhoEYpKCZWWM1MKAAwiv1U/y3xIZMohR7qbOMEkMUcZS6yF9J2toydmni3KYWqZ00S3alPA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Network ID" = _t, IPStart = _t, IPEnd = _t, IPStartNum = _t, IPEndNum = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"IPStartNum", Int64.Type}, {"IPEndNum", Int64.Type}})
    in
        #"Changed Type"
    
    IPs:
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcnBCcAwDEPRXXwOQlbi0swSsv8alQtfB/HOidxCPi8msmJEavuRk7532CGiXCNFFr2fhMWuSVzsmu4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IP = _t, IPNum = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"IPNum", Int64.Type}})
    in
        #"Changed Type"