Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Everyone,
I'm searching a solution to find a subnet from an IP and a mask in two differents columns. In an Excel file, i used the macro below:
'----------------------------------------------
' IpAnd
'----------------------------------------------
' bitwise AND
' example:
' IpAnd("192.168.1.1"; "255.255.255.0") returns "192.168.1.0"
Function IpAnd(ByVal ip1 As String, ByVal ip2 As String) As String
' compute bitwise AND from right to left
Dim result As String
While ((ip1 <> "") And (ip2 <> ""))
Call IpBuild(IpParse(ip1) And IpParse(ip2), result)
Wend
IpAnd = result
End Function
Do you know a solution to convert this VBA code in Power BI or is there any other solution ?
Thank you very much for your help !
Solved! Go to Solution.
Here this is the equivelent:
// IpParse
(_ip as text) =>
let pos = Text.PositionOf(_ip, ".", Occurrence.Last)
in
if pos = -1 then
[IpParse = Number.FromText(_ip), ip = ""]
else
[IpParse = Number.FromText(Text.Middle(_ip, pos + 1)), ip = Text.Range(_ip, 0, pos)]
// IpBuild
(ip_byte as number, ip as text) =>
let ip =
if ip <> "" then
"." & ip
else
ip
in
[ip = Text.From(Number.Mod(ip_byte, 256)) & ip, IpBuild = Int8.From(ip_byte / 256)]
Hello @VincePowerBI
you have forgotten to hand over the "IpParse"-Function that is called within the loop
jimmy
I have two other functions called in the IPand that i used to recover the subnet:
' if ip="192.168.1.32"
' IpParse(ip) returns 32 and ip="192.168.1" when the function returns
Function IpParse(ByRef ip As String) As Integer
Dim pos As Integer
pos = InStrRev(ip, ".")
If pos = 0 Then
IpParse = Val(ip)
ip = ""
Else
IpParse = Val(Mid(ip, pos + 1))
ip = Left(ip, pos - 1)
End If
End Function
' example 1:
' if ip="168.1.1"
' IpBuild(192, ip) returns 0 and ip="192.168.1.1"
' example 2:
' if ip="1"
' IpBuild(258, ip) returns 1 and ip="2.1"
Function IpBuild(ip_byte As Double, ByRef ip As String) As Double
If ip <> "" Then ip = "." + ip
ip = Format(ip_byte And 255) + ip
IpBuild = ip_byte \ 256
End Function
Thank for your help !
Here this is the equivelent:
// IpParse
(_ip as text) =>
let pos = Text.PositionOf(_ip, ".", Occurrence.Last)
in
if pos = -1 then
[IpParse = Number.FromText(_ip), ip = ""]
else
[IpParse = Number.FromText(Text.Middle(_ip, pos + 1)), ip = Text.Range(_ip, 0, pos)]
// IpBuild
(ip_byte as number, ip as text) =>
let ip =
if ip <> "" then
"." & ip
else
ip
in
[ip = Text.From(Number.Mod(ip_byte, 256)) & ip, IpBuild = Int8.From(ip_byte / 256)]
Than you very much Artemus !
Do you have an idea for the first macro that i sent ? in Excel, i used it to convert IP + Mask to a subnet
The first macro that i sent, was:
'----------------------------------------------
' IpAnd
'----------------------------------------------
' bitwise AND
' example:
' IpAnd("192.168.1.1"; "255.255.255.0") returns "192.168.1.0"
Function IpAnd(ByVal ip1 As String, ByVal ip2 As String) As String
' compute bitwise AND from right to left
Dim result As String
While ((ip1 <> "") And (ip2 <> ""))
Call IpBuild(IpParse(ip1) And IpParse(ip2), result)
Wend
IpAnd = result
End Function
Do you know how integrate and used them in power bi ?
I have the IP in a column and the mask in another column
My goal is to find the subnet following the IP (example: 192.168.1.20) in a column and the mask in another column (Example: 255.255.254.0) !
The text version (assuming you only need to use 255 or 0 in the mask):
(ip as text, mask as text) =>
let
// Example:
// ip = "192.168.1.1",
// mask = "255.255.255.0",
// returns "192.168.1.0"
m_ip = List.Buffer(Text.Split(ip, ".")),
m_mask = List.Buffer(Text.Split(mask, ".")),
result_list = if List.Count(m_ip)<> List.Count(m_mask) then null else List.Accumulate(List.Zip({m_ip, m_mask}), {}, (s,a)=> s & {if a{1} = "0" then "0" else a{0}}),
result_text = Text.Combine(result_list, ".")
in result_text
This is a "proper" bitwise version:
(ip as text, mask as text) =>
let
// Example:
// ip = "192.168.1.1",
// mask = "255.255.255.0",
// returns "192.168.1.0"
m_ip = List.Buffer(Text.Split(ip, ".")),
m_mask = List.Buffer(Text.Split(mask, ".")),
result_list = if List.Count(m_ip)<> List.Count(m_mask) then null else List.Accumulate(List.Zip({m_ip, m_mask}), {}, (s,a)=> s & {Number.ToText(Number.BitwiseAnd(Number.FromText(a{0}), Number.FromText(a{1})))}),
result_text = Text.Combine(result_list, ".")
in result_text
Kind regards,
JB
Hi,
Thank you very much for your feedback ! So, with your last code, can i get the subnet using all type of subnets ?
And last question: How can i integrate your code in my table ?
Thank you very much for your help 🙂
Use Add Column -> invoke custom function
Hi Artemus,
Thank you very much for your help. Everything is working fine 🙂