Filtering a nested table by original column

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.