cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

match IPAddress(Convert to Decimal) to a IPLocation - Slow Perf

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

1 REPLY 1
Highlighted
Microsoft
Microsoft

Re: match IPAddress(Convert to Decimal) to a IPLocation - Slow Perf

Hi @pdesmarais,

 

Here are two similar threads, please try the suggestion in below links:

Power Query - Slow data load after filtering

Power Query - extremely long load times after simple filters applied

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors