Power BI Question on Using M - Table.SelectRows

[raymondwkmok]

Hi there,

I've set the following formula for retrieving the information from the next row of the same dataset; but the outcomes from the search based on the formula below  is "null".

Table.SelectRows(
   Table.AddIndexColumn([All],"Index",1),
   (Outter)=> Outter[Index] = Table.AddIndexColumn([All],"Index",0)[Index]
)

I've tried some other functions like List.Last but cannot give the correct result; and so would like to look for advice and support from the community.

Thanks
Raymond

[v-saisrao-msft]

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!

[v-saisrao-msft · Accepted solution]

Hi @raymondwkmok ,

Thank you for reaching out to the Microsoft Forum Community.

The solution provided by @SacheeTh  has successfully addressed your query. In addition to his solution, here are some suggestions that might help you manage column name conflicts in your Power BI queries:

The error you're encountering occurs because the Table.join operation results in duplicate column names, which Power BI cannot handle (e.g., two columns named "Store"). To resolve this, you can:

1. Rename Duplicate Columns: Open Power Query Editor, identify any duplicate columns, and rename them to avoid conflicts.
2. Use Aliases: If renaming isn't an option, create aliases to differentiate columns, such as adding a suffix like
   "store1".
3. Check the Data Source: Ensure your data source doesn't have duplicate column names. If it does, modify the source data to remove duplicates.

If you have any further questions, feel free to let me know.

[dufoq3]

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]

[SacheeTh]

Hi @dufoq3 
This is a useful function for day today work, thaks for sharing. 🤗

[dufoq3]

Hi @SacheeTh, you're welcome. Enjoy 😉

[SacheeTh]

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

[SacheeTh]

Hi @raymondwkmok,
The issue with your formula is that you're trying to reference rows by their index, but the way you're doing it in Table.SelectRows and Table.AddIndexColumn should be incorrect.

Here's my suggestion to fix it:

Correct Approach to Access the Next Row

Try this Step 1st: (let us know this helps or not)

1. Add an Index column to your dataset. (i use this after all my initial transfromation is over)
2. Create a second table where the values are shifted one row down (e.g., the "next row"). 
3. Join the two tables by the Index column. (This may have a performce issue, on the join in large data sets)

Here's a step-by-step M code I rote in my PC, pls comments and do these steps\change your code as needed:

Same code

let
    // Step 1: Add an Index column to the original table you have
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    
    // Step 2: Add another Index column shifted by 1 for the next row(Addin row by row)
    NextRowIndex = Table.AddIndexColumn(Source, "NextIndex", 1, 1, Int64.Type),
    
    // Step 3: Merge the original table with the shifted table
    MergedTable = Table.NestedJoin(AddIndex, "Index", NextRowIndex, "NextIndex", "NextRowTable"),
    
    // Step 4: Expand the merged table to bring in "next row" data
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "NextRowTable", {"ColumnName"}) // Replace "ColumnName" with the column(s) you need
in
    ExpandedTable

Small Explanation, on the steps: 

1. AddIndexColumn: The Index column is added to align each row with a number starting from 0. 
2. NextRowIndex: A second index column is created, shifted by 1, to represent the "next row."
3. NestedJoin: The tables are joined on Index and NextIndex to match rows with their "next row."
4. ExpandTableColumn: The desired data from the next row is brought into the main table

What I think yuou shoul ddo to change  or to Check:

• Replace the Source with your original table's variable name.
• Replace theColumnName with the columns you want from the next row.

If you need further clarification, let me know!

[raymondwkmok]

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

[ZhangKun]

i think i got what you mean, and i give you two implementations. if its not what you want, you can try it with a line that is commented.

let
    Source = Table.FromValue(List.Transform({1, 10, 100}, each Table.FromValue({_.._+9})), [DefaultColumnName = "All"]),
    AddCutomColumn = Table.AddColumn(Source, "custom", each 
        let 
            tbl1 = Table.AddIndexColumn([All], "Index", 1), 
            tbl2 = Table.AddIndexColumn([All], "Index", 0), 
            result = Table.AddColumn(tbl1, "next Value", each try tbl2{[Index = [Index]]}[Value] otherwise null)
            //result = Table.AddColumn(tbl2, "next Value", each try tbl1{[Index = [Index]]}[Value] otherwise null)
        in result
    )
in
    AddCutomColumn

 or

let
    Source = Table.FromValue(List.Transform({1, 10, 100}, each Table.FromValue({_.._+9})), [DefaultColumnName = "All"]),
    AddCutomColumn = Table.AddColumn(Source, "custom", each 
        let 
            //valueList = {null} & List.RemoveLastN([All][Value]), 
            valueList = List.RemoveFirstN([All][Value]), 
            names = Table.ColumnNames([All]) & {"next value"}
        in 
            Table.FromColumns(Table.ToColumns([All]) & {valueList}, names)
    )
in
    AddCutomColumn

[ZhangKun]

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.