cancel
Showing results for 
Search instead for 
Did you mean: 
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!

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors