Partial text Matches in Nested Lists

[KDTF1023]

Hello, I am hoping to add a custom column in my query that would provide a true/false based on whether lists in a nested list contains a partial text match.  For example, I would like the substring text "Act" to give a true if "F25 Actual" appeared in the nested list. I have tried Text.Contains, List.ContainsAny, which worked with regular lists,  but I am getting errors with the nested list structure that I am not sure how to resolve.

Thank you

[v-karpurapud]

Hi @KDTF1023 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[v-karpurapud]

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.

[KDTF1023]

Hello and thanks all for responding,

Sample data would be something like this, with an Account Header and then 36 Columns of monthly data for each of F25 Actual, F25 Budget, F24 Actuals

After loading into the Editor I used Table.ToColumns to convert columns to lists.  Since the 3rd list, for example, includes "Actual" I'd lke the Custom column to return a TRUE if "Act" is used as the substring text in the transform function.  The list columns for 'F25 Budget' should show FALSE.

[raisurrahman]

@KDTF1023 

It would be helpful if you could share a sample dataset along with the expected output format.

[Murtaza_Ghafoor]

It looks like in your case the column contains Act then true else false, you can create conditional column in power query for an example a similar kind of power query would require to be added .

= if [NestedListColumn] = null then false else

  List.AnyTrue(

      List.Transform(

          List.Combine([NestedListColumn]),

          each Text.Contains(_, "Act", Comparer.OrdinalIgnoreCase)

      )

  )

If you can provide more information then i can write a complete code in here.

[Royel]

Hi @KDTF1023 , it seems like you are dealing with nested lists. To get an answer faster, it will be better for others if you can share a sample file of what you have and what exactly you want to achieve. 

[KDTF1023]

Hi All and thanks for responding,

Sample data would look like this with an Account column and 36 columns of monthly data for each of F25 Actual, F26 Budget, F24 Actuals

After loading into the Editor I used Table.ToColumns to convert the columns into Lists.  Since the third column  list, for example, contains "Actual" i would like the Custom column to show TRUE if "Act" is used as the substring text in the transform function.  The column lists that include "Budget" should show FALSE. 

[Royel]

@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