Filter the fifth and the sixth character of a code

[Jeffreyjar]

Hello everyone, 

I have a code like this    A10199015 

I want to filter the fifth and the sixth character of the code (or 99) in a table with power query

this is the sample data below

A10101001

A10199015

A10399001

B29901001

this the filtered table that i want (without a code that have 9(fifth character) and a 9(sixth character))

A10101001

B29901001

@ryan_mayu 

[ryan_mayu]

@Jeffreyjar 

you can try to create a new column in PQ

=Text.Middle([column],4,2)="99"

then filter the custom column

at last, delete the custom column

[Jeffreyjar]

getting now this error

@rsbin 

can i send you the .pbix file through mail?

[rsbin]

@Jeffreyjar ,

Good Morning.

You can attach your pbix file to your reply message. Below the reply box is where you can attach your file.

[Jeffreyjar]

[Jeffreyjar]

hello @rsbin 

it is giving me this error 

[rsbin]

@Jeffreyjar ,

let
    Source = YOURSOURCE
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Agents", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "5th&6th", each Text.Middle( [Agents],4, 2 ))
in
    #"Added Custom"

Both Agents and your new column should be text.  I cannot tell what is causing that error.  Works for me just fine.

[Jeffreyjar]

hello @rsbin 

thanks for the tip, but can you do it with a  specific column for example Agents

[rsbin]

@Jeffreyjar ,

When you are in Power Query, goto the last step of your Applied Steps.

Select the Add Column menu at the top and select Custom Column

In the Dialogue Box, type the following:

Text.Middle([Agents],4,2 ) 

If there are no syntax errors, click OK.

[rsbin]

@Jeffreyjar ,

As a new step in Power Query, Add a Custom Column that extracts the fifth and sixth characters:

= Table.AddColumn(#"Changed Type", "5th&6th", each Text.Middle([code],4,2 ))

  Then simply filter that column by excluding "99".

Others here may be able to do it in one step, but for clarity, my preference would be the two step approach to ensure you are excluding the correct records.

Regards,