Forum Discussion

Capitalgman987's avatar
Capitalgman987
Copper Contributor
Dec 08, 2024
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??...
excel
power bi
  • Capitalgman987's avatar
    Capitalgman987
    Dec 16, 2024

    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.

Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 11, 2024

(Can't check this) variant:

let
  Query_Begin = "SELECT [REC_NO], [FUND_CD], [SFUND_CD], [UNIT_CD] FROM [JRNL_CA]",

  lstFund_IsEmpty = List.IsEmpty( lstFund ),
  lstFY_IsEmpty = List.IsEmpty( lstFY ), 
  criteria_Fund = if lstFund_IsEmpty = true then "" else " WHERE [FUND_CD] in (" & Text.Combine( lstFund, "," ) & ")",
  criteria_FY = if lstFY_IsEmpty = true then "" else " [FY_DC] in (" & Text.Combine( lstFY, "," ) & ")",
  Query_End = if ( lstFund_IsEmpty = true ) and ( lstFY_IsEmpty = true )
                then ""
              else if ( lstFund_IsEmpty = false ) and ( lstFY_IsEmpty = false )
                then criteria_Fund & " AND " & criteria_FY
              else if ( lstFund_IsEmpty = false )
                then criteria_Fund
              else " WHERE " & criteria_FY,

  Source = Sql.Database( "SQL5200", "Data", [Query = Query_Begin & Query_End] )
in
  Source

 

Resources