Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
Brilliant, thank you Angelia!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.