Partial text Matches in Nested Lists

Hi KDTF1023 
Thank you for reaching out to the Microsoft Fabric community forum.

I have tested this with a small sample data.The issue happens because Table.ToColumns() does not include column headers. It only returns the column values as lists. So the nested lists will never contain text like “F25 Actual” or “F25 Budget”, which is why the text check is not working as expected.To identify Actual vs Budget, we need to apply the logic on column names, not on the list values.

Please refer attached .pbix file and snapshot for your reference and share your thoughts.

I hope this information is helpful. If you have any further questions, please let us know. we can assist you further.

Best Regards.
 Microsoft Fabric Community Support Team.

raisurrahman  Thank you for shareing more details 

Check this m-code 

let
    // STEP 1: Sample Source Data with updated columns
    Source = Table.FromRows(
        {
            {"Account", "Jan F25 Actual", "Feb F25 Actual", "Mar F25 Actual", "Apr F25 Budget", "May F25 Actual"},
            {"5001", "429", "456", "227", "183", "245"},
            {"5002", "222", "166", "166", "284", "198"},
            {"5003", "465", "196", "463", "391", "412"},
            {"5004", "234", "127", "101", "354", "289"},
            {"5005", "99", "464", "309", "303", "156"},
            {"5006", "498", "249", "181", "237", "321"},
            {"5007", "375", "425", "259", "337", "402"},
            {"5008", "429", "469", "361", "416", "378"},
            {"5009", "422", "161", "125", "335", "267"},
            {"5010", "299", "364", "368", "415", "344"},
            {"5011", "158", "280", "154", "339", "211"}
        },
        {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}
    ),
    
    // Promote first row to headers
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
    // STEP 2: Get Column Names
    ColumnNames = Table.ColumnNames(PromotedHeaders),
    
    // STEP 3: Convert table columns to lists
    ColumnLists = Table.ToColumns(PromotedHeaders),
    
    // STEP 4: Create table with Column Names and Data Lists
    CombinedTable = Table.FromColumns(
        {ColumnNames, ColumnLists},
        {"ColumnName", "ColumnData"}
    ),
    
    // STEP 5: Add Custom Column - TRUE if "Actual" found in column name
    AddContainsActual = Table.AddColumn(
        CombinedTable, 
        "ContainsActual", 
        each Text.Contains([ColumnName], "Actual", Comparer.OrdinalIgnoreCase),
        type logical
    ),
    #"Removed Columns" = Table.RemoveColumns(AddContainsActual,{"ColumnData"})
in
    #"Removed Columns"

Results: 

Here Account and Budget do not contains Act and its showing False 

If it helps please mark it as solution. 

Thanks