Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filter only text in address column

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

Need Help
Show and Tell
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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