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

Kidd_Ip  When either list is empty, the following error is returned: 

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

You may rely on Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn instead of using native query.  Power Query generates SQL in background, you may use M-script only.   In your case it could be

let
    lstFund = {"a", "b"},
    lstFY = {2024},
    Source = Sql.Database("SQL5200", "Data"),
    GetData = Source{[Schema="dbo",Item="JRNL_CA"]}[Data],
    SelectColumns = Table.SelectColumns(
        GetData,
        {"REC_NO", "FUND_CD", "SFUND_CD", "UNIT_CD"}
        ),
    
    FilterTable =
        if List.Count(lstFund) > 0
        then  Table.SelectRows(
            SelectColumns,
            each List.Contains(lstFund, [FUND_CD])
                and List.Contains(lstFY, [FY_DC])
            )
        else SelectColumns
in
    FilterTable

It's easy to break query folding, you may check on each step from right click menu if native query was generated till this step or not.

For the above script generated sql (which works directly on server) will be like

select [_].[REC_NO],
    [_].[FUND_CD],
    [_].[SFUND_CD],
    [_].[UNIT_CD]
from 
(
    select [REC_NO],
	[FUND_CD],
    	[SFUND_CD],
    	[UNIT_CD]
    from [dbo].[JRNL_CA] as [$Table]
) as [_]
where ([_].[FUND_CD] in ('a', 'b')) and ([_].[FY_DC] in (2024))

but in most cases you may not care which sql was generated.

(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

Hi, I don't have a SQL Server anymore to check but that should be close to:

et
  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 Query_Begin
              else if ( lstFund_IsEmpty = false ) and ( lstFY_IsEmpty = false )
                then Query_Begin & criteria_Fund & " AND " & criteria_FY
              else if ( lstFund_IsEmpty = false )
                then Query_Begin & criteria_Fund
              else Query_Begin & " WHERE " & criteria_FY,

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