List values in the next record and all subsequent records.

[Anonymous]

In PQ, I am trying to add a custom column of lists. Each list contains the values from one column and includes the valve from the current record, the next record, and all subsequent records. Note the process is done for each [id]. Something like columns A, B, and C in the image below. 

Thanks,

LAS

[ronrsnfld · Accepted solution]

Try this:

let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"Value", Int64.Type}}),

//Group by ID
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {
        {"List Below",(t)=>

    //Add an index column
    // then add the List Below column using the Index column to decide how many items
    // to remove
            let 
                #"Add Index" = Table.AddIndexColumn(t,"idx",0,1,Int64.Type),
                #"Add List Column" = Table.AddColumn(#"Add Index","List Below", each List.RemoveFirstN(t[Value],[idx]), type list)          
            in 
                #"Add List Column", 
                type table[Value=Int64.Type, List Below=list]}
        }),
        
    #"Expanded List Below" = Table.ExpandTableColumn(#"Grouped Rows", "List Below", {"Value", "List Below"})
in
    #"Expanded List Below"

Note: I did not expand the List Below column as you said you only wanted columns A, B and C 

In Power query:

[Anonymous]

Thanks,

I need to try group by custom function. Very cool.

[ronrsnfld]

It is a remarkably handy tool.