Forum Discussion
Anonymous
4 years agoNot applicable
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...
- 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"
Vijay_A_Verma
4 years agoMost Valuable Professional
What will be the logic to capture these yellow rows?
Anonymous
4 years agoNot applicable
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.