Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
desperado
Regular Visitor

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 

1 ACCEPTED SOLUTION

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:

vkellymsft_0-1623919619652.png

For the updated .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

5 REPLIES 5
desperado
Regular Visitor

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 

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

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:

vkellymsft_0-1623916170352.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

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

 

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:

vkellymsft_0-1623919619652.png

For the updated .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors