Dynamically skip or delete rows between specific values in power query

[Data_Stylist]

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 to delete all rows starting from "Total:" and ending "Product Cost $/litre" in column PRODUCT_COST($/L) , reason being that there are several instances in the table and I need to be able to delete anwhere those two shows up and anything in between.

Please find the current state, and what I'm trying to achieve (future state) below.

Current State

Source.Name	Name	PRODUCT_COST($/L)
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

Transformed State

Source.Name	Name	PRODUCT_COST($/L)
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	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	null
A.xlsx	Feb	5.12
A.xlsx	Feb	5.87
A.xlsx	Feb	4.47
A.xlsx	Feb	7.92

[mromain]

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]

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

[mromain]

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

[Data_Stylist]

I figured out the rest

[v-venuppu]

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.

[KNP]

I don't know what other columns you may have but could you filter out 'Total:' and 'Product Cost $/litre' and then group? See code below.

let
    Source = 
        Table.FromRows(
            Json.Document(
                Binary.Decompress(
                    Binary.FromText(
                        "i45WctSryCmuUNJR8krMA5JGeiZGSrE6GOKmehbm2MQN9UxMqCGeV5qTQw31IfkliTlWpOgY/OJuqUlAMqAoP6U0uUTBOb+4REFFPyezpCgVmzpc+g31TElRboQR8BBxUz0jMyoYT1vl2FIBfh20CnVTPUMj7OLo+QkibqJnglXcXM8SaE4sAA=="
                        , BinaryEncoding.Base64
                    )
                    , Compression.Deflate
                )
            )
            , let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SourceName = _t, Name = _t, PRODUCT_COST_per_L = _t]
        )
    , ChangeType = 
        Table.TransformColumnTypes(
            Source
            , {
                { "SourceName", type text }
                , { "Name", type text }
                , { "PRODUCT_COST_per_L", type text }
            }
        )
    , FilterRows = 
        Table.SelectRows(
            ChangeType
            , each (
                [PRODUCT_COST_per_L] <> "Product Cost $/litre"
                and [PRODUCT_COST_per_L] <> "Total:"
            )
        )
    , ChangeType1 = 
        Table.TransformColumnTypes(
            FilterRows
            , {
                { "PRODUCT_COST_per_L", type number }
            }
        )
    , GroupRows = 
        Table.Group(
            ChangeType1
            , { "SourceName", "Name" }
            , {
                { "Count", each List.Sum([PRODUCT_COST_per_L]), type nullable text }
            }
        )
in
    GroupRows

[Data_Stylist]

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

[KNP]

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?

[Data_Stylist]

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.