Forum Discussion
Solved
How to check if a list is blank and then take action based on the result.
I am a very new with Power Query and M. The following code works as long as the list (lstFund) has a value. If not, the query rightfully errors. How do I check if the list is empty (List.IsEmpty??...
Thank you all for your replies. I couldn't get any variation of your suggestions to work. I finally got it to work by using if List.NonNullCount(list Name)<1 then...
Thanks again.
How about this:
let
lstFund = {}, // Example list, replace with your actual list
lstFY = {}, // Example list, replace with your actual list
// Check if lstFund is empty
FundCondition = if List.IsEmpty(lstFund) then "" else """JRNL_CA"".[FUND_CD] in (" & Text.Combine(lstFund, ",") & ")",
// Check if lstFY is empty
FYCondition = if List.IsEmpty(lstFY) then "" else """JRNL_CA"".[FY_DC] in (" & Text.Combine(lstFY, ",") & ")",
// Combine conditions
CombinedCondition = Text.Combine(List.Select({FundCondition, FYCondition}, each _ <> ""), " AND "),
// Construct the query
Source = Sql.Database("SQL5200", "Data", [Query="SELECT ""JRNL_CA"".[REC_NO], ""JRNL_CA"".[FUND_CD], ""JRNL_CA"".[SFUND_CD], ""JRNL_CA"".[UNIT_CD] FROM [JRNL_CA] WHERE " & CombinedCondition])
in
Source