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

Ladies & Gents
This is my first Power Query function.
Based on above scenario here is what I figured out.
-------------------------------------------------------

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 = Text.Combine({Octet2, Octet3})
in
Result


in
IP2Number

 

-------------------------------------

Please le me know if it's correct thinking 

  • desperado's avatar
    desperado
    Regular Visitor
    5 years ago

    After testing, I need to have a check on the value of octet2.
    If the value of octet2 is negativ, then Result should be "unknown"

    Not familiar with if's .... 

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

      Hi  desperado ,

       

      Modify your M code as below:

      let
IP2Number = (

IP as text

) => let

currentIP = IP,
Alfa = {"0".."9"} & {"A".."Z"},
ListOfOctets = Text.Split(currentIP, "."),
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


in
IP2Number

      And you will see:

      For the related .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