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

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

 

VincePowerBI's avatar
VincePowerBI
Regular Visitor
6 years ago

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's avatar
    VincePowerBI
    Regular Visitor
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        VincePowerBI
        Regular Visitor
        6 years ago

        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 🙂