Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
dahya_mistry
Frequent Visitor

Problem trying to reference Column in Nested Table for Custom Function

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_IDSTART_DATEEND_DATE
71221/05/2003 00:00:0012/08/2003 00:00:00
71213/08/2003 00:00:0010/08/2004 00:00:00
71211/08/2004 00:00:0023/04/2008 00:00:00
72330/01/2003 00:00:0002/02/2003 00:00:00
72311/02/2003 00:00:0007/06/2004 00:00:00
96820/05/1998 00:00:0024/06/1998 00:00:00
96825/06/1998 00:00:0023/11/1998 00:00:00
96824/11/1998 00:00:0020/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):

 

Power Query Issue.jpg

 

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 !!

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

@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"




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

3 REPLIES 3
bhanu_gautam
Super User
Super User

@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"




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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.

@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 ?

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors