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

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 🙂

artemus's avatar
artemus
Microsoft Employee
6 years ago

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

  • VincePowerBI's avatar
    VincePowerBI
    Regular Visitor
    6 years ago

    Hi Artemus,

     

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