Conditional Left Join w/ PowerQuery - Large Dataset

Question

Here's the PowerQuery problem

(Static Application Security Testing) SAST Scanner --- gives ---> Vuln, Line #, File Path, etc.

Mapped Test Suite for Scanner --- gives ---> Function, Line Start, Line End, File Path, etc.

I put all the data in Excel.

Now I need to connect the 'Scanner' data w/ the 'Test Suite Map' data

Example Tables of datasets:

Scanner

Vuln Desc	Line #	File Path
SQL Injection	5	\TestSuite\CWE89\sql_injection.cs
SQL Injection	15	\TestSuite\CWE89\sql_injection.cs

Map

Function	Line Start	Line End	File Path
Bad_Function()	1	10	\TestSuite\CWE89\sql_injection.cs
Good_Function()	11	20	\TestSuite\CWE89\sql_injection.cs

I'm using this process to analyze how many "Good" (False Positive) and "Bad" (True Positive) functions are being detected by the scanners.

I'm trying to do this with PowerQuery and using their "M" language. I've tried to use some of the "M" language docs and also https://p3adaptive.com/2019/02/powerquerymagic-conditional-joins-using-table-selectrows/.

However, these are not working...results below.

Go into Power Query
Create a Custom Column for the 'Scanner' table

"M" Code as "Custom Column Formula"

    Table.SelectRows(Map, (map_ref)=> 
    ([#"Line #"]>=map_ref[Line Start] 
    and [#"Line #"]<=map_ref[Line End] and [File Path]=map_ref[File Path]))

I expand the new column into the matched 'Map' columns

This works for the test set of data here

Scanner (Left Joined w/Map)

Function	Line #	File Path	Scanner to Map.Function
SQL Injection	5	\TestSuite\CWE89\sql_injection.cs	Bad_Function()
SQL Injection	15	\TestSuite\CWE89\sql_injection.cs	Good_Function()

The problem is that it doesn't work for my actual data

Some thing to mention that I've already fixed:

I already made sure the file paths will match up

My Actual Tables (smaller dataset - that works)

VCG_juliet_test_results_csharp

Type Name	File Path	Line
SQL Injection	C:\Users\User_Name\Documents\SAST Benchmarking\juliet_test_suite_1.3-csharp\src estcases\CWE319_Cleartext_Tx_Sensitive_Info\CWE319_Cleartext_Tx_Sensitive_Info__NetClient_SqlConnection_05.cs	321
SQL Injection	C:\Users\User_Name\Documents\SAST Benchmarking\juliet_test_suite_1.3-csharp\src estcases\CWE319_Cleartext_Tx_Sensitive_Info\CWE319_Cleartext_Tx_Sensitive_Info__NetClient_SqlConnection_06.cs	117

juliet_test_suite_csharp_map

filepath	type	line_start	line_end
C:\Users\User_Name\Documents\SAST Benchmarking\juliet_test_suite_1.3-csharp\src estcases\CWE319_Cleartext_Tx_Sensitive_Info\CWE319_Cleartext_Tx_Sensitive_Info__NetClient_SqlConnection_05.cs	secondary good	255	334
C:\Users\User_Name\Documents\SAST Benchmarking\juliet_test_suite_1.3-csharp\src estcases\CWE319_Cleartext_Tx_Sensitive_Info\CWE319_Cleartext_Tx_Sensitive_Info__NetClient_SqlConnection_06.cs	secondary good	94	130

As you can see, these functions should also match up. The line numbers are in between the line numbers of the second table. Therefore, the "Conditional Left Join" should work here.

(Using Add Column in Power Query) Custom Code to do Conditional Join*

Table.SelectRows(juliet_test_suite_csharp_map, (map_ref)=> ([Line]>=map_ref[line_start] 
    and [Line]<=map_ref[line_end] and [File Path]=map_ref[filepath]))

VCG_juliet_test_results_csharp (joined w/ test suite map)

Type Name	File Path	Line	Juliet Mapping.filepath	Juliet Mapping.type	Juliet Mapping.line_start	Juliet Mapping.line_end
SQL Injection	C:\Users\User_Name\Documents\SAST Benchmarking\juliet_test_suite_1.3-csharp\src estcases\CWE319_Cleartext_Tx_Sensitive_Info\CWE319_Cleartext_Tx_Sensitive_Info__NetClient_SqlConnection_05.cs	321	C:\Users\User_Name\Documents\SAST Benchmarking\juliet_test_suite_1.3-csharp\src estcases\CWE319_Cleartext_Tx_Sensitive_Info\CWE319_Cleartext_Tx_Sensitive_Info__NetClient_SqlConnection_05.cs	secondary good	255	334
SQL Injection	C:\Users\User_Name\Documents\SAST Benchmarking\juliet_test_suite_1.3-csharp\src estcases\CWE319_Cleartext_Tx_Sensitive_Info\CWE319_Cleartext_Tx_Sensitive_Info__NetClient_SqlConnection_06.cs	117	C:\Users\User_Name\Documents\SAST Benchmarking\juliet_test_suite_1.3-csharp\src estcases\CWE319_Cleartext_Tx_Sensitive_Info\CWE319_Cleartext_Tx_Sensitive_Info__NetClient_SqlConnection_06.cs	secondary good	94	130

It does work here, but when we do it on a larger dataset w/ the same format we get this.

Some extra info:

the data set is around 216,000 rows

Above picture:

When I expand the column I get all null values
When I close and load, it loads about 3 rows a second. There are 200,000 rows so that would take 18.5 hours!! (my math might be off there)

Conclusion

My conditional left join works on smaller datasets w/ the same formatting and same add column formula
In Power Query after expanding the column, I'm left with all null values. Although, closing and loading the query causes it to load at super slow speeds
The problem might be the large dataset, and maybe Power Query isn't made for this. I might have to write a manual program for this larger dataset

Would like to here from people more versed in Power Query and solving these types of problems. If there is anymore info I can give you about how I'm doing feel free to ask, and I can edit my question.

Conditional Left Join w/ PowerQuery - Large Dataset

Here's the PowerQuery problem

My Actual Tables (smaller dataset - that works)

0 Answers