Forum Discussion
Solved
Dynamically skip or delete rows between specific values in power query
I am working with a bunch of merged tables containing redunandant null data, I still need to fill down some of the null data so I cannot outrighly delete all nulls. The best logic I came up with is t...
Hello Data_Stylist,
Here is a possible solution:let Source = #table( type table [Source.Name = text, Name = text, #"PRODUCT_COST($/L)" = text], { {"A.xlsx", "Jan", "2.42"}, {"A.xlsx", "Jan", "5.87"}, {"A.xlsx", "Jan", "1.44"}, {"A.xlsx", "Jan", "1.44"}, {"A.xlsx", "Jan", "1.44"}, {"A.xlsx", "Jan", null}, {"A.xlsx", "Jan", "1.44"}, {"A.xlsx", "Jan", null}, {"A.xlsx", "Jan", "Total:"}, {"A.xlsx", "Jan", null}, {"A.xlsx", "Jan", null}, {"A.xlsx", "Jan", null}, {"A.xlsx", "Jan", null}, {"A.xlsx", "Jan", null}, {"A.xlsx", "Jan", null}, {"A.xlsx", "Jan", null}, {"A.xlsx", "Jan", null}, {"A.xlsx", "Feb", "Product Cost $/litre"}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", "1.5"}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", "2.44"}, {"A.xlsx", "Feb", "5.26"}, {"A.xlsx", "Feb", "1.5"}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", "1.5"}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", "1.5"}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", "Total:"}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", "Product Cost $/litre"}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", "5.12"}, {"A.xlsx", "Feb", "5.87"}, {"A.xlsx", "Feb", "4.47"}, {"A.xlsx", "Feb", "7.92"} }), ListMaches = let findMatches = (t) => List.PositionOf(Source[#"PRODUCT_COST($/L)"], t, Occurrence.All) in List.Zip({findMatches("Total:"), findMatches("Product Cost $/litre")}), RemoveRows = List.Accumulate(List.Reverse(ListMaches), Source, (s, c)=> Table.RemoveRows(s, c{0}, c{1} - c{0} + 1)) in RemoveRowsI figured out the rest
Thanks KNP Unfortunately the solution took out the data I need as well (see screenshot below). I only need to remove rows from "Total:" and "Product Cost $/litre" while keeping everything else.
Your solution
Desired Solution
Yeah, I wasn't sure if this was the complete data or just what you're able to share.
I thought you may have other columns to group on to allow you to keep the individual rows.
At any point in the process, do you have more/other columns available that could be used for filtering?
No, I've explored the other filter options and none would work perfectly. The best solution would be to conditionally delete based on values in PRODUCT_COST_PER_L column.