Add column of previous values from table of tables in Power BI / Power Query

Question

Looking for a followup to Max Zelensky's solution here. Assuming the original example had a [Date] field, I'm trying to go one more level and add a column that shows the prior [Date] value

I've also looked here and here and here.

1. Per Max, I've created the table of tables:

   • AddedCustom = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([tmp],"Occurrence", 1,1) type table)

2. Created a second index:

   • SecondIndex= Table.AddColumn(AddedCustom, "Custom.2", each Table.AddIndexColumn([Custom],"Occurance.2", 0,1), type table)

3. I've successfully added a column that references the current [Date] row:

   • CurrentDate= Table.AddColumn(SecondIndex, "Date.2", each Table.AddColumn([Custom.2],"Date.2", each [Date]), type table)

4. But when I try to reference either index column (even just putting in {0}), the new field errors out. I'm fairly certain I'm missing something in the syntax of referencing rows within a table within a column of tables, but I'm just not sure how to get there -- A few examples I've tried without success:

   • PriorDate= Table.AddColumn(SecondIndex, "PriorDate", each Table.AddColumn([Custom.2],"Prior Date", each {0}[Date]), type table) -- just to see if I could return the value from the first row

   • PriorDate= Table.AddColumn(SecondIndex, "PriorDate", each Table.AddColumn([Custom.2],"Prior Date", each {[Occurance.2]}[Date]), type table) --doesn't work for [Occurance] or [Occurance.2]

   • PriorDate= Table.AddColumn(SecondIndex, "PriorDate", each Table.AddColumn([Custom.2],"Prior Date", each {[Occurance]-1}[Date]), type table)

   • PriorDate= Table.AddColumn(SecondIndex, "PriorDate", each Table.AddColumn([Custom.2],"Prior Date", each [Custom.2]{0}[Date]), type table)

   • PriorDate= Table.AddColumn(SecondIndex, "PriorDate", each Table.AddColumn([Custom.2],"Prior Date", each Table.SelectColums([Custom.2],[Date])), type table)

Also, can anyone point me to a good reference of the syntax and mechanics for optimizing #Tables, {Lists}, [Records] etc. I would appreciate it (I've read chapter 20 of Ken Puls' book a few times, but it hasn't quite stuck yet). Thanks in advance!

| Name | Date     | Occurance | Prior Date (Desired) |
|------|----------|-----------|----------------------|
| A    | 1/1/2019 | 1         | null/error           |
| A    | 3/1/2019 | 2         | 1/1/2019             |
| B    | 2/1/2019 | 1         | null/error           |
| A    | 4/1/2019 | 3         | 3/1/2019             |
| B    | 5/1/2019 | 2         | 2/1/2019             |

Answer 1

Similar to my answer here, instead of adding just one index, you can add two, one starting from 0 and one starting from 1, which we use to calculate the previous row by performing a self merge.

let
    Source = Table.FromRows({{"A",#date(2019,1,1)},{"A",#date(2019,1,3)},{"B",#date(2019,1,2)},{"A",#date(2019,1,4)},{"B",#date(2019,1,5)}}, {"Name", "Date"}),
    ChangeTypes = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    GroupByName = Table.Group(ChangeTypes, {"Name"}, {{"tmp", each _, type table}}),
    AddIndices = Table.AddColumn(GroupByName, "Custom", each Table.AddIndexColumn(Table.AddIndexColumn([tmp],"Occurrence", 1,1),"Prev",0,1)),
    ExpandTables = Table.ExpandTableColumn(AddIndices, "Custom", {"Date", "Occurrence", "Prev"}, {"Date", "Occurrence", "Prev"}),
    SelfMerge = Table.NestedJoin(ExpandTables,{"Name", "Prev"},ExpandTables,{"Name", "Occurrence"},"Expanded Custom",JoinKind.LeftOuter),
    ExpandPriorDate = Table.ExpandTableColumn(SelfMerge, "Expanded Custom", {"Date"}, {"Prior Date"}),
    RemoveExtraColumns = Table.RemoveColumns(ExpandPriorDate,{"Prev", "tmp"})
in
    RemoveExtraColumns