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

KNP's avatar
KNP
Super User
1 year ago

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

    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's avatar
      KNP
      Super User
      1 year ago

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

        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.