web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / Power BI Question on U...
Customer experience | Sales, Customer Insights,...
Answered

Power BI Question on Using M - Table.SelectRows

(1) ShareShare
ReportReport
Posted on by RM-06090351-0 9
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
BI Problem 1.jpg
Categories:
Dynamics 365 Customer Insights - Data
I have the same question (0)
  • Suggested answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 2,354 Moderator on at

    My response was crafted with AI assistance, tailored to provide detailed and actionable guidance for your query.

    Your issue seems to stem from the fact that you're trying to compare rows within the same dataset without properly aligning or iterating through the rows. The Table.SelectRows function cannot directly reference a row relative to the current one unless you process the table in a way that makes the comparison explicit. 

    Goal: Retrieve data from the next row of the same dataset.

    Below is a step-by-step solution using Power Query M:

    Solution:

    1. Add an Index Column to Identify Row Order
      Use Table.AddIndexColumn to add a unique row index for referencing.

    2. Join the Table with Itself (Offset by One)
      Duplicate the table and shift the index in one of the copies by +1 to align each row with the next.

    3. Perform a Self-Join on Index
      Use Table.Join to link the original table with the shifted version using the index columns.

    Here’s the corrected M code to achieve this:

    m
    let
    // Step 1: Add an Index Column (starting from 0)
    SourceWithIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),

    // Step 2: Add another Index Column (shifted by +1)
    SourceWithNextIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

    // Step 3: Perform a Self-Join
    MergedTable = Table.Join(
    SourceWithIndex, "Index",
    SourceWithNextIndex, "Index",
    JoinKind.LeftOuter
    ),

    // Step 4: Extract Relevant Columns (e.g., current row + next row data)
    FinalTable = Table.SelectColumns(MergedTable, {"Column1", "Column2", "Column1.1", "Column2.1"})
    in
    FinalTable

    Explanation:

    1. SourceWithIndex: Adds an "Index" column starting at 0.
    2. SourceWithNextIndex: Creates a second version of the table but starts the index at 1.
    3. MergedTable: Joins the original table (SourceWithIndex) with the shifted table (SourceWithNextIndex) on the Index column. This essentially lines up each row with its next row.
    4. FinalTable: Select only the desired columns from the joined table, including the original data and data from the next row.

    Key Notes:

    • Replace Source with the name of your table.
    • Replace Column1, Column2, etc., with the names of the columns in your dataset.
    • If your dataset needs to handle the last row (which will have no "next row"), you can use conditional logic to handle null values.

    Why Your Code Didn't Work:

    • You are trying to directly compare Outter[Index] with the Index of another computed table (Table.AddIndexColumn with Index = 0) without explicitly aligning rows.
    • The function Table.SelectRows works row-by-row, and without proper alignment, it cannot understand the relationship to the "next row."
  • Suggested answer
    RM-06090351-0 Profile Picture
    RM-06090351-0 9 on at
    Hi Saif Ali Sabri,

    Thanks for your advice and I've used the suggested formula into my BI but got the following error: -

    Expression.Error: A join operation cannot result in a table with duplicate column names ("Store").
    Details:
        [Type]

    It seems the error was due to the same column name, coz "Store" is the 1st column and so it became the 1st error pop out. 

    Would pretty much like to know how to avoid the duplicate column name issue please.

    Many Thanks
    Raymond

  • Verified answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 2,354 Moderator on at
    The error you're encountering occurs because the Table.Join operation produces a table with duplicate column names. When you join two tables that contain columns with the same name (e.g., "Store"), Power BI cannot handle the resulting duplicate column names.
    Solution: Rename or Remove Duplicated Columns During the Join.
                   Rename the Duplicate Column Before the Join.
     

    • Rename Duplicate Columns: Identify and rename any duplicate columns in your dataset. You can do this by going to the Modeling tab in Power BI and selecting Transform Data to open Power Query Editor. Look for columns with the same name and rename one of them.

    • Use Aliases: If renaming columns is not an option, you can use aliases in your formula to differentiate between the columns. For example:

      Table.SelectRows( Table.AddColumn(YourTable, "Store1", each [Store]), (Row) => Row[Store1] = "desired_value" )

      This way, you're explicitly referencing the columns with different names.

    • Check Data Source: Ensure that your data source does not have duplicate column names. If it does, you may need to modify the source data to remove duplicates.

  • Saif Ali Sabri Profile Picture
    Saif Ali Sabri 2,354 Moderator on at 
    Other Solutions

1. **Use Merge Queries instead of Join**: Power Query's Merge Queries allows specifying which columns to include in the result.
 * Go to Home tab > Merge Queries.
 * Select the common column and choose the columns you want to include.
2. **Drop duplicate columns after joining**: Use the `Table.RemoveColumns` function.


    ```powerquery
= Table.RemoveColumns(
    JoinedTable,
    {"Store"}
)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

#1
ManoVerse Profile Picture

ManoVerse 184 Super User 2026 Season 1

#2
11manish Profile Picture

11manish 125

#3
CU11031447-0 Profile Picture

CU11031447-0 100

Last 30 days Overall leaderboard

Featured topics

Hide selecting Contact for potential customer when creating quote

Product updates

Dynamics 365 release plans