Table.ReferenceDifferentRow.pq

let func =   
 (Table as table, optional Step as number, optional SelectedColumns, optional GroupByColumns, optional Suffix as text, optional Buffer as any) =>

let
// Steps to prepare the (optional) parameters for the nested function "fnFetchNextRow"
    Source = if Buffer = null then Table else Table.Buffer(Table),
    Step0 = if Step = null then -1 else Step,
    Step_ = if Step = null then 1 else Number.Abs(Step),
    Suffix = if Suffix = null then ".Prev" else Suffix,
    GroupByColumns = if GroupByColumns = null then null else GroupByColumns,
    ShiftFunction = if Step0 < 0 then Table.RemoveLastN else Table.RemoveFirstN,
    ColNames = List.Buffer(Table.ColumnNames(Source)),
    NewColNames = if SelectedColumns = null then ColNames else SelectedColumns,
    CountNewCols = List.Count(NewColNames),

// Core function that retrieves values from previous or next rows (depending on sign of parameter "Step")
    fnFetchNextRow = (Table_ as table, optional Step as number, optional SelectedColumns, optional Suffix as text, optional Buffer as any) =>
        let
            MergeTable = if SelectedColumns = null then Table_ else Table.SelectColumns(Table_, SelectedColumns),
            Shift = if Step0 > 0 then ShiftFunction(MergeTable, Step_) & #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_))
                                else #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_)) & ShiftFunction(MergeTable, Step_),
            Reassemble = Table.ToColumns(Table_) & Table.ToColumns(Shift), 
            Custom1 = Table.FromColumns( Reassemble,  Table.ColumnNames(Source) & List.Transform(NewColNames, each _&Suffix ) )
        in
            Custom1,

// optional grouping on certain columns
    #"Grouped Rows" = Table.Group(Source, GroupByColumns, {{"All", each _}}, GroupKind.Local),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fnFetchNextRow([All], Step0, SelectedColumns, Suffix, Buffer)),
    #"Removed Columns" = Table.Combine(Table.RemoveColumns(#"Added Custom", GroupByColumns & {"All"})[Custom]),

// case no grouping
    NoGroup = fnFetchNextRow(Source, Step0, SelectedColumns, Suffix, Buffer),

// select case grouping
    Result = if GroupByColumns = null then NoGroup else #"Removed Columns"
in
    Result ,
documentation = [
Documentation.Name =  " Table.ReferenceDifferentRow ",
Documentation.Description = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
Documentation.LongDescription = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
Documentation.Category = " Table ",
Documentation.Source = "  ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann (www.TheBIccountant.com ) ",
Documentation.Examples = {[Description =  "  ",
Code = " fnTable_ReferenceDifferentRow( #table( {""Product"", ""Value""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""} } ) ) ) ",
Result = " #table( {""Product"", ""Value"", ""Product.Prev"", ""Value.Prev""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""}, {null ,""A"" ,""A"" ,""B"" ,""B""}, {null ,""1"" ,""2"" ,""3"" ,""4""} } ) ) "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))