Filter only text in address column

[Anonymous]

Hello all,

i  need your help to filter a address column that only have the text value (see example)

How can i only return the rows that contains only text / addressname without housenumber?

So for example i need only return the memberid 2342234 and 221111

Any suggestions?

Greets,

Rega

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please refer to this blog to modify the Power Query to filter data records.

Power Query reference:

let
    CharsToRemove = List.Transform({33..45,47,58..126}, each Character.FromNumber(_)),
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true),
    Test3_Sheet = Source{[Item="Test3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Test3_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"memberid", Int64.Type}, {"value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Text.Remove([value],CharsToRemove)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Result] = ""))
in
    #"Filtered Rows"

Best regards,

Yuliana Gu