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
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
RemoveRowsThanks mromain The solution worked for the sample data I provided, but when applied to the actual data which contains thousands of row, I got the range error below.
the first step (ListMaches) of extracting list that matched the appropriate conditions worked well, the extraction step is where there seems to be an issue. Any fix?
Expression.Error: The 'count' argument is out of range.
Details:
-5I figured out the rest
Hi Data_Stylist ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you KNP mromain for the prompt response.
I want to check whether you are able to resolve the issue..?If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hello,
It's hard to answer without knowing your source data.
However, I can reproduce the error with this dataset where “Total:” is after “Product Cost $/liter” for the second occurrence.
Maybe it's the same problem with your source data.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", "Product Cost $/litre"}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", null}, {"A.xlsx", "Feb", "Total:"}, {"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 RemoveRows
