Power BI Question on Using M - Table.SelectRows

(1) Share

Report

Posted on by RM-06090351-0

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

All responses (4)

Answers (1)

Saif Ali Sabri 370 on at

Like (0)

Report
Power BI Question on Using M - Table.SelectRows

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"} )
Verified answer

Saif Ali Sabri 370 on at

Like (0)

Report
Power BI Question on Using M - Table.SelectRows

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.
Suggested answer

RM-06090351-0 9 on at

Like (0)

Report

Power BI Question on Using M - Table.SelectRows

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
Suggested answer

Saif Ali Sabri 370 on at

Like (1)

Report
Power BI Question on Using M - Table.SelectRows

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:

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

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.

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:

SourceWithIndex: Adds an "Index" column starting at 0.

SourceWithNextIndex: Creates a second version of the table but starts the index at 1.

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.

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."