Convert VBA Excel Code to Power BI

[VincePowerBI]

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 !

[artemus · Accepted 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)]

[Jimmy801]

Hello @VincePowerBI 

you have forgotten to hand over the "IpParse"-Function that is called within the loop

jimmy

[VincePowerBI]

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 !

[artemus · Accepted 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)]

[VincePowerBI]

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

[VincePowerBI]

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

[Anonymous]

Hi @VincePowerBI 

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

[VincePowerBI]

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 🙂

[artemus · Accepted solution]

Power Query Editor Add Column, followed by Invoke Custom FunctionUse Add Column -> invoke custom function

[VincePowerBI]

Hi Artemus,

Thank you very much for your help. Everything is working fine 🙂