Forum Discussion
Power Query - Nested table - List.PositionOf - Find substring
This line of M code works perfectly...
RowIndexOfCashFlow = Table.AddColumn(AddTableWithIndex, "Row Index of Cash Flow", each List.PositionOf([Data][Column3], "Cash Flow Analysis - Checking *7114:", Occurrence.First))
...but I would like to search for the substring "Cash Flow" and not the entire text. What do I need to do in order to accomplish that?
Hard to tell without a data sample. But if your list is coming from a nested table, and there are nulls in that list (in the column of the nested table), you will see that error.
You can correct for it by changing the comparer to handle nulls:(x,y)=> Text.Contains(x ?? "",y))?? is the coalesce operator and will convert null to an empty string.
If that is not the issue, please provide a sanitized example of the actual list you are feeding into the formula. Be sure when you sanitize it that it still reproduces the error.
5 Replies
use List.FindText instead of List.PositionOf
You can use the 4th argument:
List.PositionOf(Source, "Cash Flow", Occurrence.First, (x,y)=>Text.Contains(x,y))If you use Occurrence.All, it will return a list of positions.
Thank you for your response but that doesn't work for some reason. It returns Error for each row.
RowIndexOfCashFlow = Table.AddColumn(AddTableWithIndex, "Row Index of Cash Flow", each List.PositionOf([Data][Column3], "Cash Flow", Occurrence.First, (x,y)=>Text.Contains(x,y)))
Expression.Error: We cannot convert the value null to type Logical.
I need the List.PositionOf from each nested table (Data) to return the position number (row number) from the list of values in Column3 where "Cash Flow" exists. The original code that I have works perfectly (returns the position number in the list of values from [Data][Column3]) but I would like to only search for "Cash Flow" and not the full text that is in Column3 (i.e., "Cash Flow Analysis - Checking *7114:"). This string only exists one time in each nested table of Data in Column3.
Any suggestions you may have would be greatly appreciated. Thanks!
Hard to tell without a data sample. But if your list is coming from a nested table, and there are nulls in that list (in the column of the nested table), you will see that error.
You can correct for it by changing the comparer to handle nulls:(x,y)=> Text.Contains(x ?? "",y))?? is the coalesce operator and will convert null to an empty string.
If that is not the issue, please provide a sanitized example of the actual list you are feeding into the formula. Be sure when you sanitize it that it still reproduces the error.
