cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AM_XRX
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
v-huizhn-msft
Microsoft
Microsoft

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

 

 

 

View solution in original post

2 REPLIES 2
v-huizhn-msft
Microsoft
Microsoft

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

 

 

 

Brilliant, thank you Angelia!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors