Forum Discussion

desperado's avatar
desperado
Regular Visitor
5 years ago
Solved

IP address conversion

Hi I have a column with IP address  I want to do following: Excample Ip address 172.180.180.34 Octet 2 Take the number from octet 2 and substract it with 176. Get the number and choose a chara...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  desperado ,

     

    Then use below code:

    let
IP2Number = (

IP as text

) => let

currentIP = IP,
Alfa = {"0".."9"} & {"A".."Z"},
ListOfOctets = Text.Split(currentIP, "."),
Octet2 = Alfa{Value.Subtract(Value.FromText(ListOfOctets{1}),176)},
Octet3 = Number.ToText(Value.FromText(ListOfOctets{2}), "x"),
Result = try Text.Combine({Octet2, Octet3}) otherwise "unknown"
in
Result


in
IP2Number

    And you will see:

    For the updated .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

desperado's avatar
desperado
Regular Visitor
5 years ago

Hi v-kelly-msft 

Thank you for the answer.
It works for octet2, but I want as follow:
If octet2 is negativ, then skip octet3 and set result to "unknown"
So for IP 172.172.180.4 the result should be "unknown" and not "unknownb4"

Again, thank you for the reply

 

Octet2 = try Alfa{Value.Subtract(Value.FromText(ListOfOctets{1}),176)} otherwise "unknown",
Octet3 = Number.ToText(Value.FromText(ListOfOctets{2}), "x"),
Result = Text.Combine({Octet2, Octet3})
in
Result

 

v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi  desperado ,

 

Then use below code:

let
IP2Number = (

IP as text

) => let

currentIP = IP,
Alfa = {"0".."9"} & {"A".."Z"},
ListOfOctets = Text.Split(currentIP, "."),
Octet2 = Alfa{Value.Subtract(Value.FromText(ListOfOctets{1}),176)},
Octet3 = Number.ToText(Value.FromText(ListOfOctets{2}), "x"),
Result = try Text.Combine({Octet2, Octet3}) otherwise "unknown"
in
Result


in
IP2Number

And you will see:

For the updated .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!