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

[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.