Forum Discussion

Data_Stylist's avatar
Data_Stylist
New Member
1 year ago
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...
General Comment
Need Help
Tips and Tricks
  • mromain's avatar
    mromain
    1 year ago

    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
    RemoveRows
  • Data_Stylist's avatar
    Data_Stylist
    1 year ago

    I figured out the rest

mromain's avatar
mromain
Regular Visitor
1 year ago

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
    RemoveRows
Data_Stylist's avatar
Data_Stylist
New Member
1 year ago

Thanks 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:
-5