Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

delete rows in power query

Hello everyone, I'm new with power bi, I would like to dynamically erase these lines in yellow non power query, how do I do that?   link file: https://docs.google.com/spreadsheets/d/1qm9_a...
  • AlexisOlson's avatar
    4 years ago

    The key here is to find the maximal 0 row for each index run and then keep only the rows with an index greater or equal to the maximal zero row index.

     

    Here's sample code for your example that you can paste into the Advanced Editor of a new blank query:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddPLFYQgDAXQXly7IF+wFo/9tzESgkJwFuIZvQTIc85zA+B8bPsG9RKkdN/rhamOkGA/5EB7eO03RwQIHFDuUZrnHdv7rmNx0GJw4ti5SOAo9n7S1HVJUWtetsKPjrUJqqZJi2tKJWq2+XNxdc4SuYLa9p821p/5L8+qa1vKX16s+lz86Lp1tGLBN8+R9jAFA7W5NkDCIcfMAbaZZJCGBK38CHFd3fOjZXX7kABH6+mxNSYeSibq0TFGaudRetLz0Fgi/NirJ8b6tf5MPS1ZjtUefGUlGPtq28TxH+iS4gbeqpgsMM9KlqwYlvXvJl/XDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code_operation = _t, branch = _t, code = _t, #"amount day" = _t, Amount = _t, price = _t, Index = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"code_operation", Int64.Type}, {"branch", Int64.Type}, {"code", Int64.Type}, {"amount day", Int64.Type}, {"Amount", Int64.Type}, {"price", Int64.Type}, {"Index", Int64.Type}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([amount day] = 0)),
        #"Grouped Rows" = Table.Group(#"Filtered Rows", {"branch"}, {{"Max0", each List.Max([Index]), type nullable number}}),
        #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"branch"}, #"Grouped Rows", {"branch"}, "Grouped Rows", JoinKind.LeftOuter),
        #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Max0"}, {"Max0"}),
        #"Filtered Rows1" = Table.SelectRows(#"Expanded Grouped Rows", each ([Index] >= [Max0])),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Max0"})
    in
        #"Removed Columns"