Forum Discussion
Mapping IP address towards IP subnets
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"
As I'm quite new to Power BI and DAX i figured out that this will take me quite a long time, and I will probably choose the wrong solution as well. Anyone done this before, or have an idea for the best solution. If so, I guess it will be reused by others since this is quite a common problem in IT
I would suggest you to use the second solution you have mentioned above. After creating a table with all IP addresses within all the subnets, you can just use column IP-Address to create a relationship between IP-Address table and Subnet table.
As the IP-Address is already mapped into the Subnet through the relationship, no any other lookups between the IP-Address table and the Subnet table is needed.
Regards
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"
Hey pade,
Can you share the powerbi file please, because I am trying to implement the code you shared in my project, and I still get errors :(
Thanks.
dorin.vlas "at" gmail.com
DV
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)
