How to check if a list is blank and then take action based on the result.

[GregHQ]

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??) and then if it is empty, skip that statement and go onto the next line. If it is not empty, I want to run that line and then check the next line to see if that list (lstFY) is empty. Thanks for any help/direction you can give me.

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

""JRNL_CA"".[FUND_CD] in (" & Text.Combine(lstFund, ",") & ")

AND ""JRNL_CA"".[FY_DC] in (" & Text.Combine(lstFY, ",") & ")

in

Source

[v-pagayam-msft]

Hi @GregHQ ,
Glad that your query has been resolved. If our community member's response addressed your question, please Accept it as  Answer. Click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!

[v-pagayam-msft]

Hi @GregHQ ,
Glad that your query has been resolved. If our community member's response addressed your question, please Accept it as  Answer. Click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!

[Poojara_D12]

Hi @GregHQ 

To handle empty lists in your Power Query M code:

1. Check if the list is empty:

   • Use List.IsEmpty(lstFund)  to check if lstFund  is empty.
   • Use List.IsEmpty(lstFY) to check if lstFY is empty.

2. Build query dynamically:

   • Start with a base query: "SELECT ... FROM [JRNL_CA] WHERE 1=1".
   • Add conditions only if the lists are not empty:
     • For lstFund: Add FUND_CD IN (...) if lstFund is not empty.
     • For lstFY: Add FY_DC IN (...) if lstFY is not empty.

3. Combine everything:

   • Combine the base query with the conditions to form the final query.

4. Execute the query:

   • Pass the dynamically created query to Sql.Database.

This ensures your code works even if the lists are empty.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[v-pagayam-msft]

Hi @GregHQ ,
I just wanted to kindly follow up to see if you had a chance to review the previous response provided by me. Please let me know if it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it more quickly.

[Anonymous]

You don't need to write custom SQL for that. If you buffer your list like

Table.SelectRows(Table, each List.Contains(List.Buffer([List]), [ColumnToCheck])

Your list will become an IN statement when the query folds.

--Nate

[v-pagayam-msft · Accepted solution]

Hi @GregHQ ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.

According to @lbendlin , please provide sample data that fully addresses your issue or question, in a usable format. As a workaround, kindly follow the approach outlined below to prevent the errors you are encountering. Based on the code you provided, it would be beneficial to include conditional checks to handle cases where an empty list may be encountered.
let

    // Example lists

    lstFund = {"FUND1", "FUND2"},  // replace by actual list

    lstFY = {},  // Empty list

    // Build WHERE clause for lstFund if not empty

    FundCondition = if List.IsEmpty(lstFund) then "" else "AND ""JRNL_CA"".[FUND_CD] in (" & Text.Combine(lstFund, ",") & ")",

    // Build WHERE clause for lstFY, if not empty

    FYCondition = if List.IsEmpty(lstFY) then "" else "AND ""JRNL_CA"".[FY_DC] in (" & Text.Combine(lstFY, ",") & ")",

    // Combine conditions together

    QueryCondition = FundCondition & " " & FYCondition,

    // SQL query to add conditions if they exist

    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 1=1 " & QueryCondition]

)

in

    Source         

Hope this post helps you. If so, kindly consider accepting it as the solution so that it can assist other members and help them find the answer more quickly.

Thank you.

[ZhangKun]

because of the "Query Folding ", you can simply load the entire table (without writing the query expression) and then filter the two columns in Power Query UI.

[lbendlin]

 and then if it is empty, skip that statement and go onto the next line.

That is not possible. You need to provide an alternative output.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...