cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AM_XRX Frequent Visitor
Frequent Visitor

List as input criteria for Text.StartsWith

I would like to filter a column based on "Begins With" criteria from another query.

 

In query "IP_Range_Criteria" I have the following table which are the non-zero octets for some IP Address ranges:

 

Ranges

11.9

12.62

12.144

...

 

In the table I want to filter I have the following column that I want to filter to values that begin with any value in the above Ranges list:

 

IP Address

11.22.54.44

11.9.2.82

12.144.15.46

12.253.56.34

...

 

If I try to use the below, I receive the error "Expression.Error: We cannot convert a value of type List to type Text."

 

= Table.SelectRows(previous step, each Text.StartsWith([IP Address], IP_Range_Criteria[Ranges]))

 

Is there a way to make this work or an alternate method to accomplish the goal?

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: List as input criteria for Text.StartsWith

Hi @AM_XRX,

I combine the two tables into one, and it's easy to comparable, please review the following steps.

1.  Add a customer coumn using the formula.

= Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(Table1,"Allranges",each Table1))


2. Expand the Ranges column, you will get the following table.

2.PNG

3. I filter the table using Text.StartsWith function. Then delete the Ranges column, just leave the expected Ip Address column, you will get the expected result.

3.png

Here is my Power Query statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcrBCcAgEAXRXjzLwH7/LrEWsf82EpQc3zBrtQgk0tht9+OJeHQlwiYS1x+UgyzG9+8X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"IP Address" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IP Address", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(Table1,"Allranges",each Table1)),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Ranges"}, {"Custom.Ranges"}),
    #"A"=Table.SelectRows(#"Expanded Custom1", each Text.StartsWith([IP Address], [Custom.Ranges])),
    #"Removed Columns" = Table.RemoveColumns(A,{"Custom.Ranges"})
in
    #"Removed Columns"


Best Regards,
Angelia

 

 

 

2 REPLIES 2
v-huizhn-msft Super Contributor
Super Contributor

Re: List as input criteria for Text.StartsWith

Hi @AM_XRX,

I combine the two tables into one, and it's easy to comparable, please review the following steps.

1.  Add a customer coumn using the formula.

= Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(Table1,"Allranges",each Table1))


2. Expand the Ranges column, you will get the following table.

2.PNG

3. I filter the table using Text.StartsWith function. Then delete the Ranges column, just leave the expected Ip Address column, you will get the expected result.

3.png

Here is my Power Query statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcrBCcAgEAXRXjzLwH7/LrEWsf82EpQc3zBrtQgk0tht9+OJeHQlwiYS1x+UgyzG9+8X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"IP Address" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IP Address", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(Table1,"Allranges",each Table1)),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Ranges"}, {"Custom.Ranges"}),
    #"A"=Table.SelectRows(#"Expanded Custom1", each Text.StartsWith([IP Address], [Custom.Ranges])),
    #"Removed Columns" = Table.RemoveColumns(A,{"Custom.Ranges"})
in
    #"Removed Columns"


Best Regards,
Angelia

 

 

 

AM_XRX Frequent Visitor
Frequent Visitor

Re: List as input criteria for Text.StartsWith

Brilliant, thank you Angelia!