delete rows in power query

[Anonymous]

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_awLgSJz4QPXPm5EFHDIULOOWwd51/edit?usp=sharing&ouid=10262...

[AlexisOlson · Accepted solution]

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"

[Anonymous]

great Alexis I'll test the code.

[Vijay_A_Verma]

What will be the logic to capture these yellow rows?

[Anonymous]

Hi Vijay, 

start cutting from the zero value in the index column and end it before the next zero value in the day quantity column, but this is only if there is more than one zero value in the day quantity column only.