Forum Discussion
Replace query value with null when it has no data
Got it, adjusted the query below:
let
DefaultCert = "'N/A'", // Ensure the default value is properly quoted
FilteredCerts = List.Select(UniqueCerts, each _ <> null),
CertsList = if List.Count(FilteredCerts) > 0 then Text.Combine(FilteredCerts, ",") else DefaultCert,
Query = "Select val1, val2, val3 from table INNER JOIN table2 ON table.id = table1.oid where CONCAT(table.val, table1.val1) in (" & CertsList & ") and val3= 'ABC'"
in
Sql.Database("server", "db", [Query=Query])This would work but I figured it out the best option is to skip the sql query as a whole if the UniqueCerts list is empty, and used an empty default table. Thanks for your help
Got it, adjusted the query below:
let
DefaultCert = "'N/A'", // Ensure the default value is properly quoted
FilteredCerts = List.Select(UniqueCerts, each _ <> null),
CertsList = if List.Count(FilteredCerts) > 0 then Text.Combine(FilteredCerts, ",") else DefaultCert,
Query = "Select val1, val2, val3 from table INNER JOIN table2 ON table.id = table1.oid where CONCAT(table.val, table1.val1) in (" & CertsList & ") and val3= 'ABC'"
in
Sql.Database("server", "db", [Query=Query])
This would work but I figured it out the best option is to skip the sql query as a whole if the UniqueCerts list is empty, and used an empty default table. Thanks for your help