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 character from a variabel
Variabel = "0123456789abcdefghijklmnopqrstuvwxyz"
So if the octet 2 is 180 the result would be:
180-176 = 4
Value after postion 4 from the variabel = 4


Octet3
Convert the number to hex
So octet 3 is 180 and would be B4

Result in new column
result octet 2+result octet 3
3B4 

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!

     

5 Replies

  • 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!