Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter out unique rows which follow the same text pattern

I have Google Analytics data and I have 102 rows (and counting) that hold data I need to filter out in Query Editor.    The column is called [Page] and below are examples of the data.   Is there ...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

    Sorry for misunderstanding. For the sample data, the rows that you want to remove contain "-". If all the actual data is like this, you could try:

    = if Text.Contains([Column1],"-") then null  else [Column1])

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc09CoQwEEDhu6Q2OJn8n2WxmCQTENHIEvb8a7NaCBZbPz7e6yVG2vc2b33lrY8MwNVnKxFTkByVkhSwSABEEwBCBBSDePPaPiym4cYN+ZS0JCB9cn9xcv6Rx5BtmKUp/91rOpCRrlA9OV3clPjEWZFGW2TlTD+u8OBQNAVXvHUPPLW2HHlh3u8xt61T7mefvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"-") then null  else [Column1])
in
    #"Added Custom"

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

v-xuding-msft  I actually ended up doing something similar - I used extract after delimiter but that meant also removing those that I wanted to keep e.g. /appointment/book and /appointment/contact but it seemed there was no solution which involved using wildcards to depict the specific pattern I wanted to filter out. 

v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi Anonymous ,

Sorry for misunderstanding. For the sample data, the rows that you want to remove contain "-". If all the actual data is like this, you could try:

= if Text.Contains([Column1],"-") then null  else [Column1])

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc09CoQwEEDhu6Q2OJn8n2WxmCQTENHIEvb8a7NaCBZbPz7e6yVG2vc2b33lrY8MwNVnKxFTkByVkhSwSABEEwBCBBSDePPaPiym4cYN+ZS0JCB9cn9xcv6Rx5BtmKUp/91rOpCRrlA9OV3clPjEWZFGW2TlTD+u8OBQNAVXvHUPPLW2HHlh3u8xt61T7mefvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"-") then null  else [Column1])
in
    #"Added Custom"

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.