Forum Discussion

pade's avatar
pade
Advocate III
9 years ago
Solved

Mapping IP address towards IP subnets

This has been challenging me for a while   I have a list with all our network connected devices (a lot of them...), and I have the entire corperate (as good as it can be) IP Subnet list, see tables...
Need Help
  • pade's avatar
    pade
    9 years ago

    So I did, and ended up with this code. I utilized the R Script library "iptools" for this, not a perfect solution since it requires R installed, but it works. Since I'm not a "native writing" programmer for neither R, Power Query, or DAX, this code might not be the best, but

     

    let
    Source = Excel.Workbook(File.Contents("C:\xxx\IP_Networks.xlsm"), null, true),
    #"IP Ranges" = Source{[Item="IP Ranges",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"IP Ranges"),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Subnets", type text}}),
    #"Run R Script" = R.Execute("# 'dataset' holds the input data for this script#(lf)library(iptools)#(lf)output <- dataset#(lf)for (t in 1:dim(dataset)[1]) {output[t,""IP Addresses""]=paste0(range_generate(as.character(dataset[t,""Subnets""])),collapse="","")}",[dataset=#"Changed Type"]),
    output = #"Run R Script"{[Name="output"]}[Value],
    #"Added Custom" = Table.AddColumn(output, "IP Address", each Text.Split([IP Addresses],",")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"IP Addresses"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns1", "IP Address")
    in
    #"Expanded Custom"

pade's avatar
pade
Advocate III
9 years ago

So I did, and ended up with this code. I utilized the R Script library "iptools" for this, not a perfect solution since it requires R installed, but it works. Since I'm not a "native writing" programmer for neither R, Power Query, or DAX, this code might not be the best, but

 

let
Source = Excel.Workbook(File.Contents("C:\xxx\IP_Networks.xlsm"), null, true),
#"IP Ranges" = Source{[Item="IP Ranges",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"IP Ranges"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Subnets", type text}}),
#"Run R Script" = R.Execute("# 'dataset' holds the input data for this script#(lf)library(iptools)#(lf)output <- dataset#(lf)for (t in 1:dim(dataset)[1]) {output[t,""IP Addresses""]=paste0(range_generate(as.character(dataset[t,""Subnets""])),collapse="","")}",[dataset=#"Changed Type"]),
output = #"Run R Script"{[Name="output"]}[Value],
#"Added Custom" = Table.AddColumn(output, "IP Address", each Text.Split([IP Addresses],",")),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"IP Addresses"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns1", "IP Address")
in
#"Expanded Custom"

cnschulz's avatar
cnschulz
Helper IV
6 years ago

Thank you. Thank you. Thank you. 🙂

 

2020 Issues I found:

1. You need to use R 3.5.0

2. Even if you have iptools installed under 4.0 and then install 3.5.0, you *must* uninstall iptools and the reinstall it to make sure that it is installed under 3.5.0 (even though it will recognise it in R, Power BI will not find it)