Adding A Cell as a Column to an Inner Table

[Nahum]

Hello,

I'm experiencing some difficulities with the Table.TransformColumns function, specifically, referencing a cell in the row that contains the table I'm modifying. I understand that what I'm doing is complicated to explain in words (or at least, I find it so), so I've provided visuals and a sample excel file that contains some toy data to illustrate it.

My data happens to be very easily nested and also very large. As one of my penultimate steps is a grouping sum/countrows, I want to avoid working with the whole dataset whenever possible. To this end, I try to be smart about how I join tables, and try to avoid expanding tables into columns until I'm completely done with my calculations.

To do those calculations, however, I need to bring my "DateOfInterest" into those inner tables as a column. 

I've prepared a sample data sheet with an example query. The step I'm stuck on is clearly listed. This is the query:

let
    Source = Table.NestedJoin(Locations, {"Location ID"}, People, {"Location ID"}, "People", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "DatesOfInterest", each #"DatesOfInterest"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Locations"}, {{"All Tables", each _, type table [Locations=nullable text, Location ID=nullable number, People=table, Custom=table]}}),
    #"Expanded DatesOfInterest" = Table.TransformColumns(#"Grouped Rows",
    {"All Tables",
        (TableRow) => Table.ExpandTableColumn(TableRow, "DatesOfInterest", {"Dates"})}),

    // This is the step I'm stuck on

    #"STEP IN QUESTION" = Table.TransformColumns(#"Expanded DatesOfInterest",
    {"All Tables",
        (OuterTableRow) => Table.TransformColumns(OuterTableRow,
        {"People",
            (InnerTableRow) => Table.AddColumn(InnerTableRow, "DateOfInterest", each OuterTableRow[Dates])})}),

    // I want whatever the OuterTableRow[Dates] value is for that row as a column in the OuterTableRow[People] table
    // This, apparently, is beyond me.

    // These steps below are just so that there's something to look at. 

    Arrakis = #"STEP IN QUESTION"{[Locations="Arrakis"]}[All Tables],
    People1 = Arrakis{0}[People]
in
    People1

A link to the sample excel sheet (including the input data) is here. 

Thank you for your consideration. If you need additional details, or have other advice on how to speed up big group-by calculations, I'll be right here. 

[ThxAlot]

Simple enough. Table.ReplaceValue() does the trick if you're looking to bring [Dates] column to the calcualtion based on inner tables.

[AlexisOlson · Accepted solution]

The Table.TransformColumns function transforms columns independently. You cannot reference Column B in when defining a transformation on Column A. You can reference multiple columns when defining a new custom column or using Table.ReplaceValue.

I don't really understand what your ultimate goal is here but I don't think you'll likely make things more efficient by doing nested table operations rather than expanding tables.

[Nahum]

I'm sorry, I just saw I never replied/accepted anything on this post. Thank you for pointing me in the right direction. 

Rethinking about how I was coming at the problem ended up being the solution. There had been a previous case where working within inner tables ended up being the nicest solution, which is why I was attempting to do the same here.