Power BI - comparing row value with column from another table results in excessive computational load

Question

I'm analyzing the logs from a firewall and I wanted to add two columns in Power Query M to determine if either the source or the destination ip address are LAN addresses or from the Internet.

I created a file called Private_IPs.txt that contains row by row all the internal subnets (10. , 172.16., 172.17. etc) and loaded it as a table.

The code for the calculation is this:

#"Add dst isPrivate" = Table.AddColumn(#"Add src isPrivate", "dst_isPrivate", each List.Count(let tmp_dst = [dest_ip] in List.Select(Table.Column(Private_IPs, "Subnet"), each Text.StartsWith(tmp_dst, _))))

1. It creates a list by selecting the column "Subnet" of the table Private_IPs
2. From this lists, it only keeps the element that [dest_ip] starts with, if any.
3. It then counts the numbers of items in the filtered list, so that my resulting column value is either 0 or 1

It works but the problem is that when I Refresh the data, it loads the file "PrivateIPs.csv" two times for each row of the table, resulting in minutes and minutes of loading time and the counter reports something like

"10MB from Private_IPs.csv"

"20MB from Private_IPs.csv"

[...]

"400MB from Private_IPs.csv"

Why does this happen? Shouldn't it keep the table in memory instead of reading from the file each time? How do I make it so? It's only a text file with 17 rows in it, maybe my solution is too convoluted?

Answer 1

Since the table with the private subnets was very small and it won't change, I created it manually inside the Query Editor and it's now only loading once.