Problem trying to reference Column in Nested Table for Custom Function

[dahya_mistry]

I am essentially attempting to create a user defined function which shows the value of the next row for a date column, for any given Person ID. Here is some sample data:

PERSON_ID	START_DATE	END_DATE
712	21/05/2003 00:00:00	12/08/2003 00:00:00
712	13/08/2003 00:00:00	10/08/2004 00:00:00
712	11/08/2004 00:00:00	23/04/2008 00:00:00
723	30/01/2003 00:00:00	02/02/2003 00:00:00
723	11/02/2003 00:00:00	07/06/2004 00:00:00
968	20/05/1998 00:00:00	24/06/1998 00:00:00
968	25/06/1998 00:00:00	23/11/1998 00:00:00
968	24/11/1998 00:00:00	20/12/2007 00:00:00

The following is what I am essentialy trying to achieve (ignore the "error", I know how to fix this using a try and otherwise statement):

however in my code I have had to hard code the column name (START_DATE) to get it to work, what I can't figure out, is how to refer to that column name in the nested table in the code, as I get various errors depending on what I try.

The function does this by:

1. Sorting the Data by the Person ID and Date

2. Creating a Group By nested Table and adding an Index Column to it

3. The next step  is where I am having problems with finding a solution. I want to add another column to this nested table which retrieves the value of the date in the next row for each person.

The function has the following definition:

fn_GetNextDates(PreviousStep, GroupByColumn, NextRowColumn, NewColumnName)

Where:

• PreviousStep - Previous Step Name
• GroupByColumn - Group By Column Name
• NextRowColumn - The Date Column you want the next date for
• NextOrPreviousDate as number - The number of Dates you want to traverse in rows as a Number
• NewColumnName - The name you want for the new column

Here is the code:

(PreviousStep as table, GroupByColumn, NextRowColumn, NextOrPreviousDate as number,  NewColumnName) =>

let

// First need to sort the rows according to the input column NextRowColumn

    #"Sorted Rows" = Table.Sort(PreviousStep,{{GroupByColumn, Order.Ascending}, {NextRowColumn, Order.Ascending}}),

    // Group the Rows by the Input Column (GroupByColumn)

    #"Grouped Rows" = Table.Group(#"Sorted Rows", {GroupByColumn}, {{"All Rows", each _ }}),

    // Add an Index in the Sub Table to order the rows

    #"Added Index Column" = Table.AddColumn(#"Grouped Rows", "AddedIndex", each Table.AddIndexColumn([All Rows], "Index", 0)),

    #"Added Next Date Column" = Table.AddColumn(#"Added Index Column", "Next Date Tables", 

each  let

    AllDataTable = [AddedIndex],

                        MyCol = AllDataTable[START_DATE], // This is my issue, this is hard coded, but I need a column reference here, using the funciton input parameter NextRowColumn !

        PreviousRowValue = Table.AddColumn(AllDataTable, NewColumnName, each MyCol{[Index] + NextOrPreviousDate }  ) // Add one to the Index Column to get the next date

                        in PreviousRowValue                        

                    )

    // Remove all the other superfluous columns                    

    // #"RemoveColumns" = Table.RemoveColumns(#"Added Next Date Column",{"AddedIndex", "All Rows", GroupByColumn}),

    // Expand the Table

    // #"Expanded Table" = Table.Combine(#"RemoveColumns"[Next Date Tables])                  

in

   #"Added Next Date Column"

I would really appreciate some help here, I've tried millions of different combinations of solutions, but none work so far, and I'm nearly there with the code !!

[bhanu_gautam · Accepted solution]

@dahya_mistry , Try using below mentioned M-Code

m
(fn_GetNextDates as table, GroupByColumn as text, NextRowColumn as text, NextOrPreviousDate as number, NewColumnName as text) =>
let
// First need to sort the rows according to the input column NextRowColumn
#"Sorted Rows" = Table.Sort(fn_GetNextDates,{{GroupByColumn, Order.Ascending}, {NextRowColumn, Order.Ascending}}),

// Group the Rows by the Input Column (GroupByColumn)
#"Grouped Rows" = Table.Group(#"Sorted Rows", {GroupByColumn}, {{"All Rows", each _ }}),

// Add an Index in the Sub Table to order the rows
#"Added Index Column" = Table.AddColumn(#"Grouped Rows", "AddedIndex", each Table.AddIndexColumn([All Rows], "Index", 0)),

#"Added Next Date Column" = Table.AddColumn(#"Added Index Column", "Next Date Tables",
each let
AllDataTable = [AddedIndex],
MyCol = Table.Column(AllDataTable, NextRowColumn), // Dynamically reference the column using Table.Column
PreviousRowValue = Table.AddColumn(AllDataTable, NewColumnName, each try MyCol{[Index] + NextOrPreviousDate} otherwise null) // Add one to the Index Column to get the next date
in
PreviousRowValue
),

// Remove all the other superfluous columns
#"RemoveColumns" = Table.RemoveColumns(#"Added Next Date Column",{"AddedIndex", "All Rows", GroupByColumn}),

// Expand the Table
#"Expanded Table" = Table.Combine(#"RemoveColumns"[Next Date Tables])

in
#"Expanded Table"

[dahya_mistry]

omg @bhanu_gautam , thats amazing, it works, thank you so much !! I tried lots of variations to reference the column in the nested table, but couldn't get anything to work. I even tried Record.Field(_, ColumnName), but that didn't work either.

[dahya_mistry]

@bhanu_gautam  can I ask why you decided to change the very first input parameter from PreviousStep to fn_GetNextDates ? I believe that they both work, I'm just intrigued why you decided to make that change ?