Power BI Question on Using M - Table.SelectRows

Table.SelectRows(
   Table.AddIndexColumn([All],"Index",1),
   (Outter)=> 
      // following expression means like 1 = {0,1,2}
      // the result always false
      Outter[Index] = Table.AddIndexColumn([All],"Index",0)[Index]
)

I don't know what your data source is, cant give further advice.

Hi ZhangKun,

Thanks for the response. 

In short, I've grouped the entire data into a column named "All". Then use the above formaula for retrieving the information from the next row of the same dataset by: -
1. Grouping all data rows into a column named [All];
2. Creating in the formula a Dataset 1 by adding Index Column (start from 1) at [All];
3. Creating in the formula a Dataset 2 by adding Index Column (start from 0) at [All];
4. Map the same Index at Dataset 1 & Dataset 2 then retrive the next row at Dataset 2

So, the source data is indeed being grouped in a column named [All].

Thanks
Raymond

Hi raymondwkmok, I've created for you a function where you can choose:

1. how many columns you want to shift (3rd parameter columns as a list) -> this one is optional, if you leave it blank, it will shift all the columns from the table.
2. which direction up or down (2nd parameter shift as number) -> positive number means shift down (to see previous rows), negative up (to see next rows) -->this is also optional parameter. If you leave it blank it will shift down by 1 row to see previous row.

• function preserves types
• this one should be significantly faster than shifting with index columns

For example, if you want to shift 3 rows down for columns [C1] and [C3], you should use it this way:

(don't forget that 1st parameter of a function requires a table, so usually previous step name)

= fn_ShiftRows(Source, 3, {"C1", "C3"})

Function:

(tbl as table, optional shift as number, optional columns as list) =>

    [
        cols = List.Buffer(columns ?? Table.ColumnNames(tbl)),
        sh = shift ?? 1,
        
        selectedCols = Table.SelectColumns(tbl, cols),
        shifted = Table.FromColumns(
                        Table.ToColumns(tbl) &
                        Table.ToColumns(
                              Table.FromRows(
                                  [ shiftDown = List.Repeat({List.Repeat({null}, List.Count(cols))}, Number.Abs(sh)) &
                                                Table.ToRows(Table.RemoveLastN(selectedCols, Number.Abs(sh))),
                                    shiftUp = Table.ToRows(Table.RemoveFirstN(selectedCols, Number.Abs(sh))) &
                                                List.Repeat({List.Repeat({null}, List.Count(cols))}, Number.Abs(sh)),
                                    check = if sh > 0 then shiftDown else shiftUp
                                  ][check]
                              )
                        ),
                        Value.Type( 
                            [ a = Table.FirstN(tbl, 0),
                              colnames = List.Transform(cols, each _ & (if sh > 0 then "_Prev" else "_Next") ),
                              colnamesZip = List.Zip({ cols, colnames }),
                              b = Table.RenameColumns(Table.FirstN(selectedCols, 0), colnamesZip),
                              c = a & b
                            ][c]
                        )
                )
                
    ][shifted]

This M code creates a table where each row is matched with data from the next row. It adds an index column to the table, shifts the index to represent the next row, and joins the original table with the shifted table. Finally, it expands the joined data to include the desired columns from the next row.

let
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    NextRowIndex = Table.AddIndexColumn(Source, "NextIndex", 1, 1, Int64.Type),
    MergedTable = Table.NestedJoin(AddIndex, "Index", NextRowIndex, "NextIndex", "NextRowTable"),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "NextRowTable", {"ColumnName"}) // Replace "ColumnName" with the column(s) you need
in
    ExpandedTable

Hi raymondwkmok ,

Just following up as we haven’t received a response to our previous message. We hope your issue has been resolved.

If the solution I provided has worked for you, kindly mark it as the accepted solution.

Thank you for being a valued member of the Microsoft Fabric Community Forum!