Filter data and delete rows when a condition is met

badapa start a blank query in PQ,  click advanced editor and paste the following code. You will see all the steps that you can apply on your data.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydnF1U4rVgTH1DAxReEZgno+vn39AIBITrCwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Col", "Col - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Col", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Col.1", "Col.2"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Col.1"}, {{"Count", each List.Max([#"Col - Copy"]), type nullable text}})
in
    #"Grouped Rows"

 

 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.