Power Query / M: check if next row exists

[Dicko]

I want to add a custom column with the value from column 'message', if that next row exists, for else I get an error.

Now I have:

Table.AddColumn(#"Added Index", "next", each if #"Added Index"{[Index]+1} then #"Added Index"{[Index]+1}[message]
  else null)

But this gives errors for all rows.

What am I doing wrong?

[wdx223_Daniel · Accepted solution]

Table.AddColumn(#"Added Index", "next", each let a=#"Added Index"{[Index]+1}? in if a=null then null else a[message])

[Anonymous]

Are you trying to get the value of the next row in column [Message]?

[Dicko]

Yes, but only if a next row exists can I get a value.
For if the next row does not exist I get an error using my code.

[jennratten]

Hello - there is a great custom function to retreive the values of next/previous rows by Imke Feldmann. 

https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po... 

RESULT (Data table)

I added two columns as an example - one for getting data from the next row and another for getting data from a previous row.

SCRIPT fnGetDifferentRow

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

SCRIPT Data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lEyVIrViVZKSswDQiDXCMwtSE1MzgDyjMG84pKixPKk1KKiSqCQCVgovSixAKTbVCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Quantity = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Quantity", Int64.Type}}),
    // Source of Custom Function: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/
    AddNextRowValue = fnGetDifferentRow(
        ChangeType, // table to transform
        1,          // 1 = get value from the next row
        {"Fruit"},  // list of columns to add with next row values
        null,       // list of columns by which the data should be grouped
        ".next",    // suffix to add to the end of the new column names
        null        // buffer option
    ),
    AddPrevRowValue = fnGetDifferentRow(
        AddNextRowValue,
        null,       // null/default = get value from the previous row
        {"Fruit"},  // list of columns to add with next row values
        null,       // list of columns by which the data should be grouped
        ".prev",    // suffix to add to the end of the new column names
        null        // buffer option
    )
in
    AddPrevRowValue

[Dicko]

Thanks for the solution for getting values if the next row exists.
Not my question, but nice to have for another time!