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

Notifications

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
Categories:
Dynamics 365 Customer Insights - Data
BI Problem 1.jpg
I have the same question (0)
  • Suggested answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 2,351 Super User 2025 Season 2 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,351 Super User 2025 Season 2 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,351 Super User 2025 Season 2 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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

#1
Tom_Gioielli Profile Picture

Tom_Gioielli 170 Super User 2025 Season 2

#2
#ManoVerse Profile Picture

#ManoVerse 61

#3
Gerardo Rentería García Profile Picture

Gerardo Rentería Ga... 52 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Hide selecting Contact for potential customer when creating quote

Product updates

Dynamics 365 release plans