Filtering a table by a list using text contained within the column

[Tieryn]

Hi,

I have a column called "textPayload" which is basicaly a license log containing strings such as the below.

OUT: "Studio_Runtime" StudioAdmin@license-server

I have also created a list of the License Features called LicenseList that I want to filter out of the whole set.

List

Studio_Runtime

Other_Licenses

etc...

I have several million rows of data coming from BigQuery, so rather than multiple steps to split up the textPayload before filtering, I'd like to try and do this in place in one step, to speed up the overall processing time of the dataset.

I can successfully do what I want when the above text is Split by Delimiter (space) so I get Studio_Runtime (or other license features) in its own column - textPayload.2, then I can do the following

Table.SelectRows(#"Filtered Rows", each List.Contains(LicenseList, textPayload.2))

This works as "textPayload.2" is the identical value as something I have in my List. But this requires me to pre-split the dataset (which just takes ages). My aim is to filter the dataset down before splitting to speed things up.

What I would like to do instead is the following, replacing the split "textPayload.2" with just extracting the text from the "textPayload" string to compare against the list.

Table.SelectRows(#"Filtered Rows", each List.Contains(LicenseList, Text.BetweenDelimiters([textPayload], """", """")))

However when I try to do this I get 

Unexpected error: Object reference not set to an instance of an object.
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.Mashup.Engine.Ast.ExpressionToMVisitor.WriteRecord(IRecordValue record)
at Microsoft.Mashup.Engine.Ast.ExpressionToMVisitor.WriteValue(IValue value)
at Microsoft.Mashup.Engine.Ast.ExpressionToMVisitor.WriteRecord(IRecordValue record)
at Microsoft.Mashup.Engine.Ast.ExpressionToMVisitor.WriteValue(IValue value)
at Microsoft.Mashup.Engine.Ast.ExpressionToMVisitor.WriteRecord(IRecordValue record)
at Microsoft.Mashup.Engine.Ast.ExpressionToMVisitor.WriteValue(IValue value)
at Microsoft.Mashup.Engine.Ast.ExpressionToMVisitor.VisitConstant(IConstantExpression2 constant)

I'm not sure what I'm doing wrong, and would appreciate any help/explanations 😄

Cheers!

[sturlaws]

Hi @Tieryn 

I created a very simple mockup according to your description, and I am unable to recreate your issue, so your code works.

As to why you get the error message, are you sure [textPayload] exists in #"Filtered Rows"?

[Tieryn · Accepted solution]

Hey @sturlaws - okay thanks, that's helpful information.

Actually - from a completely separate issue I was looking into I think I've found the answer.

It's probably because i'm importing from BigQuery - but it seems like what was missing was a  

#"Buffer table"= Table.Buffer(#"Filtered Rows"),

After adding this in advanced editor it worked.