Create a TRUE/FALSE column on one table based on multiple columns combinations of another table

[nomad7_gr]

Hello,

I am trying to create a TRUE/FALSE column called "is_excluded" in table "Merged Vulnerabilities", based on combinations of multiple columns of table "Exclusions". To give you more context, I have an external Excel file with vulnerabilities exclusion data which I load into table "Exclusions". Table "Merged Vulnerabilities" holds my vulnerabilities data.

The mapping between the two tables is done based on the following criterion:

Exclusions Table	Merged Vulnerabilities Table
Device	normalized_asset
Software	normalized_software
CVE	normalized_cve

Depending on the Excel file and thus the contents of table "Exclusions", I may have several combinations that I should consider when trying to exclude (is_excluded = TRUE) records/rows/vulnerabilities on table "Merged Vulnerabilities". More specifficaly:

1. Blank Device - Blank Software - Specific CVE: This applies to all devices and software for the specified CVE.

2. Blank Device - Specific Software - Specific CVE: This applies to all devices with the specified software and CVE.

3. Blank Device - Specific Software - Blank CVE: This applies to all devices with the specified software, regardless of CVE.

4. Specific Device - Blank Software - Specific CVE: This applies to the specified device for any software with the specified CVE.

5. Specific Device - Blank Software - Blank CVE: This applies to the specified device for any software and any CVE.

6. Specific Device - Specific Software - Blank CVE: This applies to the specified device with the specified software, regardless of CVE.

7. Specific Device - Specific Software - Specific CVE: This applies to the specified device with the specified software and CVE.

Note that I will never have a row in the Excel file and thus on table "Exclusions" that will have all three columns blank.

My goal is to go through all rows of table "Merged Vulnerabilities" and check the normalized_asset, normalized_software and normalized_cve if they match with one of the above combinations and respectively flag them with is_excluded = TRUE or is_excluded = FALSE.

What I have done so far is the below code in table "Exclusions" query and in table "Merged Vulnerabilities" query:

Exclusions query:

let
    Source = Excel.Workbook(Web.Contents("https://my_exclusions_excel_file.xlsx"), null, true),
    Exclusions_Sheet = Source{[Item="Exclusions",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Exclusions_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type logical}, {"Column6", type logical}, {"Column7", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] = "my_client_name_here"),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Client"}, {"Column2", "Device"}, {"Column3", "Software"}, {"Column4", "CVE"}, {"Column5", "FP"}, {"Column6", "RA"}, {"Column7", "Reason"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns", "", null, Replacer.ReplaceValue, {"CVE"}),

    // Aggregate Exclusions
    Aggregated_Exclusions = Table.Group(#"Replaced Value", {"Client", "Device", "Software", "CVE"}, {
        {"FP", each List.Contains([FP], true), type logical},
        {"RA", each List.Contains([RA], true), type logical},
        {"Reason", each Text.Combine(List.Distinct([Reason]), "; "), type text}
    }),
    #"Added Index" = Table.AddIndexColumn(Aggregated_Exclusions, "ExclusionID", 1, 1, Int64.Type)
in
    #"Added Index"

The above produces a power bi table like the below:

Client	Device	Software	CVE	FP	RA	Reason	ExclusionID
my_client		jre		FALSE	TRUE	Some reason	22

Merged Vulnerabilities query:

    // My previous steps here...

    // Reference the Exclusions query
    Aggregated_Exclusions = #"Exclusions",

    // Ensure the join operation is correctly set up
    #"Merged Exclusions" = Table.NestedJoin(
        round_risk_score, 
        {"normalized_asset", "normalized_software", "normalized_cve"}, 
        Aggregated_Exclusions, 
        {"Device", "Software", "CVE"}, 
        "Exclusions", 
        JoinKind.LeftOuter
    ),

    // Expand the joined table to include exclusion columns
    #"Expanded Exclusions" = Table.ExpandTableColumn(
        #"Merged Exclusions", 
        "Exclusions", 
        {"Device", "Software", "CVE"}, 
        {"Exclusions.Device", "Exclusions.Software", "Exclusions.CVE"}
    ),

    // Determine if a row should be excluded
    is_excluded = Table.AddColumn(#"Expanded Exclusions", "is_excluded", each 
        let
            deviceMatch = ([Exclusions.Device] = null or [Exclusions.Device] = "" or [Exclusions.Device] = [normalized_asset]),
            softwareMatch = ([Exclusions.Software] = null or [Exclusions.Software] = "" or [Exclusions.Software] = [normalized_software]),
            cveMatch = ([Exclusions.CVE] = null or [Exclusions.CVE] = "" or [Exclusions.CVE] = [normalized_cve]),
            exclusionValid = (
                ([Exclusions.Device] <> null and [Exclusions.Device] <> "") or 
                ([Exclusions.Software] <> null and [Exclusions.Software] <> "") or 
                ([Exclusions.CVE] <> null and [Exclusions.CVE] <> "")
            )
        in
            exclusionValid and deviceMatch and softwareMatch and cveMatch
    )
in
    is_excluded

The above produces a table in power bi with many columns, including normalized_asset, normalized_software, normalized_cve (columns I add in previous steps and I want them matched with Exclusions combinations) and also is_excluded.

The code works for most of its part, but for some reason it doesnt work for the example I posted in Exclusions excel file regarding the "jre" Software. I would expect that it would flag with is_excluded = TRUE all Merged Vulnerabilities rows that have ANY normalized_asset (blank Device in excel file so treated as wildcard), that also have "jre" as normalized_software and also have ANY normalized_cve (blank CVE in excel file so treated as wildcard).

Unfortunately, these rows are marked with is_excluded = FALSE. Other combinations seem to work, for example for entries in the Excel file that have a specific asset, specific software and blank CVE, the respective rows in Merged Vulnerabilities are marked with is_excluded = TRUE as it should.

Would appreciate any help you can give.

Thank you!

[Anonymous]

Hi @nomad7_gr ,

Based on the description, try to add the following judgment after is_include.

check_exclusion = Table.AddColumn(is_excluded, "is_excluded_final", each 
        let
            device = if [Exclusions.Device] = "" then true else [Exclusions.Device] = [normalized_asset],
            software = if [Exclusions.Software] = "" then true else [Exclusions.Software] = [normalized_software],
            cve = if [Exclusions.CVE] = "" then true else [Exclusions.CVE] = [normalized_cve]
        in
            device and software and cve
    ),
    final_table = Table.RemoveColumns(check_exclusion, {"is_excluded"})
in
    final_table

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nomad7_gr]

Hi @Anonymous ,

Thanks for your answer, unfortunately the issue was not resolved as still the rows that have normalized_software = jre are still with is_excluded_final = FALSE. But I noticed another problem with these rows. The Exclusions.Software column is null instead of having the value "jre" and perhaps or obviously this is the reason why is_excluded_final results in FALSE.

I suspect that the joins between the 2 tables (Merged Vulnerabilities and Exclusions tables) are not happening correctly, but I can't locate why. I checked the column names between the 2 tables (so normalized_software for Merged Vulnerabilities and Software for Exclusions) and they are correct. I also checked the value "jre" of normalized_software column of Merged Vulnerabilities table matches the value "jre" of Software column of Exclusions table.

Thanks!