Filtering a nested table by original column

[StefanoT1982]

I have a nested table inside the column "VALIDATION CON SCONTO PROMO + EXTRA POST PROMO" where I want to set to 0 the values of some columns after a certain date:

= Table.TransformColumns

    (#"TY con Cust prof sconto promo+Extra post promo",

    {"VALIDATION CON SCONTO PROMO+EXTRA POST PROMO",

    each 

    [eliminapostpromo =

        Table.FromRecords

            (Table.TransformRows(_,   (r) => Record.TransformFields(r,

                  {{"SU ", each if r[DATA]> #date(2025,09,15) then 0 else _},

                  {"Promo", each if r[DATA]> #date(2025,09,15) then 0 else _},

                   {"Extra", each if r[DATA]> #date(2025,09,15) then 0 else _},

                   {"Promo+Extra", each if r[DATA]> #date(2025,09,15) then 0 else _}

                   })))][eliminapostpromo] })

This formula works, but now instead of setting to 0 all values where the nested column DATA is beyond Sep 15th 2025 I'd like to do it referring to the value of the column [PROMO END] inside the original table. I've tried to work it around with some "let/in" syntax but couldn't find any working solution...

thanks for your help!

[tayloramy · Accepted solution]

Hi @StefanoT1982, 

 
Table.TransformColumns applies a function to the value of the target column only (your nested table). That function doesn’t naturally “see” sibling columns like [PROMO END]. To reference [PROMO END], iterate the outer table with Table.TransformRows, grab the outer row value, and then transform the nested table with it.

Quick solution (use the outer row to drive the nested transform):

let
    // Your starting table
    Source = #"TY con Cust prof sconto promo+Extra post promo",

    // Transform each OUTER row so we can read [PROMO END]
    WithAdjustedNested =
        Table.FromRecords(
            Table.TransformRows(
                Source,
                (outer as record) =>
                    let
                        promoEnd = outer[#"PROMO END"],            // capture per-row value
                        innerTbl = outer[#"VALIDATION CON SCONTO PROMO+EXTRA POST PROMO"],

                        // Rewrite the INNER table row-by-row
                        innerAdjusted =
                            Table.FromRecords(
                                Table.TransformRows(
                                    innerTbl,
                                    (r as record) =>
                                        Record.TransformFields(
                                            r,
                                            {
                                                {"SU ",       each if r[DATA] > promoEnd then 0 else _},
                                                {"Promo",     each if r[DATA] > promoEnd then 0 else _},
                                                {"Extra",     each if r[DATA] > promoEnd then 0 else _},
                                                {"Promo+Extra", each if r[DATA] > promoEnd then 0 else _}
                                            }
                                        )
                                    )
                            ),

                        // Replace the nested column in this OUTER row
                        outerAdjusted =
                            Record.TransformFields(
                                outer,
                                {
                                    { "VALIDATION CON SCONTO PROMO+EXTRA POST PROMO", (x)=> innerAdjusted }
                                }
                            )
                    in
                        outerAdjusted
            )
        )
in
    WithAdjustedNested

Notes:

• Make sure [PROMO END] and the nested [DATA] are typed as date. If needed:

  Source = Table.TransformColumnTypes(
      Source,
      {{"PROMO END", type date}}
  );

• If [PROMO END] can be null, guard the comparison:

  each if (promoEnd <> null and r[DATA] > promoEnd) then 0 else _

• Your original pattern using Table.TransformRows + Record.TransformFields for the inner table is spot-on; the change above just lifts [PROMO END] from the outer row into that logic.

Docs:
Table.TransformRows
Record.TransformFields
Table.FromRecords

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.