Forum Discussion
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?
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.
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.
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
v-huizhn-msftMicrosoft Employee
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.
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.
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,
AngeliaBrilliant, thank you Angelia!
