Forum Discussion
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:
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"
4 Replies
What will be the logic to capture these yellow rows?
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"
