power query filter issue

[mmoore5553]

i am trying to get power query to filter out some rows by looking at a column and find cells that are 2 characters and then have 5 numbers.

I have searched the forum with no results. I did try to use wild card in text filter but nothing worked.

If i need to put an example I can

I just have rows with names and then numbers

Thank you so much. I am just learning power query and love it.

ex.

mm73423
hm23902

 

[alansidman]

I used the following scenario. Filter all items beginning with "mm" and having a length of 7 characters

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if(Text.Length([Column1]))=7 then [Column1] else ""),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if(Text.Start([Custom],2)="mm") then [Custom] else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

 

[mmoore5553]

I am not sure that is going to work. It is based on name. So it would not be "mm" all the time. It will always be first 2 spots are characters and second spot is numbers - 5

by reading what you typed it would grab 7. I guess i could work off that but really wanted it to validate and make sure only certain characters and numbers.

 

[alansidman]

Since you didn't provide details (criteria) for which characters, I gave you an example to work with. If you wish to expand on your criteria then I am sure we here at this forum can find a viable solution.

BTW: Since you are responding directly to one person, there is no need to quote the prior message as it is only clutter. Suggest you click on the reply button unless there is confusion between posts that require clarification.

 

[AliGW]

If you want bespoke help, then attach a sample workbook.

 

[Bill Szysz]

Hi mmoore5553,
Try this code below

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each (Text.Length([Data]) = 7) and (    try Logical.From(Number.From(Text.End([Data], 5)))  otherwise false    ) and ( List.AllTrue(List.Transform(Text.ToList(Text.Start([Data],2)), each try not (Logical.From(Number.From(_))) otherwise true)) )  )
in
    #"Filtered Rows"

Of course, change my header (Data) to yours.

 

[mmoore5553]

Bill

Thank you so much. Next time i will add samples. I am still trying to read and understand most of the code.