Forum Discussion

VincePowerBI's avatar
VincePowerBI
Regular Visitor
6 years ago
Solved

Convert VBA Excel Code to Power BI

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 !

Need Help
  • artemus's avatar
    artemus
    6 years ago

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

     

  • artemus's avatar
    artemus
    6 years ago

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

9 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello VincePowerBI 

     

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

     

    jimmy

    • VincePowerBI's avatar
      VincePowerBI
      Regular Visitor
      6 years ago

      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's avatar
        artemus
        Microsoft Employee
        6 years ago

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