I need some of your expertise on the following topics.
I want to match IPAddress(Convert to Decimal) to a IPLocation Database in SQL
I have a table with 30,000 entries with IP Address. Im able easily to convert to Decimal with a function i created.
Once i get the IP in Decimal then i call a function that does a filter in a table, but this process is very very slow.
There are other solutions like calling a Web Service to get the IP Address, it is way faster, but the PBI Service online doesn't support the function (Unless you have a trick).
Source table with Ip Address -> Function IPDecimal -> Custom Column withIP As Decimal (FAST) -> Function FilterTablewithIPDecimal for each row (SLOW)
See functions below.
I tried (SQL Select as well)
Any suggestions
SOURCE TABLE THAT NEEDS convert to IPDecimal and match Region.
let
Source = Oracle.Database("eIPPEprd", [HierarchicalNavigation=true]),
EIPPE = Source{[Schema="EIPPE"]}[Data],
VIEW_SESSTRCKNG1 = EIPPE{[Name="VIEW_SESSTRCKNG"]}[Data],
#"Filtered Rows" = Table.SelectRows(VIEW_SESSTRCKNG1, each not Text.StartsWith([IPADDRESS], "10.")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"SESSIONID", "LOGGEDIN", "FAILEDATTEMPT", "CHANGEDUSER"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each Date.IsInCurrentMonth([LOGINTIME])),
#"Added Custom" = Table.AddColumn(#"Filtered Rows2", "CUSTOM", each IPtoDecimal([IPADDRESS]))
in
#"Added Custom"
IPtoDecimal Function
let
SQLSource = (IP as text) =>
let
IP0 = Number.FromText(Text.Split(IP, "."){0}),
IP1 = Number.FromText(Text.Split(IP, "."){1}),
IP2 = Number.FromText(Text.Split(IP, "."){2}),
IP3 = Number.FromText(Text.Split(IP, "."){3}),
IPDecimalFirst = Value.Add(Value.Multiply(IP0,16777216),Value.Multiply(IP1,65536)),
IPDecimalLast = Value.Add(Value.Multiply(IP2,256),IP3),
IPDecimal = Value.Add(IPDecimalFirst,IPDecimalLast),
//IPDEC = IPLocationFilter(Text.From(IPDecimal)),
IPDEC = IPLocationFilterTest(IPDecimal),
//Source = IPDecimal,
#"Filtered Rows1" = Table.SelectRows(SVR_ip2location_db5, each [ip_from] <= IPDecimal),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [ip_to] >= IPDecimal),
Source = #"Filtered Rows2"
in
Source
in
SQLSource
