Forum Discussion
Power BI Question on Using M - Table.SelectRows
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:
- Rename Duplicate Columns: Open Power Query Editor, identify any duplicate columns, and rename them to avoid conflicts.
- Use Aliases: If renaming isn't an option, create aliases to differentiate columns, such as adding a suffix like
"store1". - 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.
Resolver IIThis 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)
- Add an Index column to your dataset. (i use this after all my initial transfromation is over)
- Create a second table where the values are shifted one row down (e.g., the "next row").
- 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 ExpandedTableSmall Explanation, on the steps:
- AddIndexColumn: The Index column is added to align each row with a number starting from 0.
- NextRowIndex: A second index column is created, shifted by 1, to represent the "next row."
- NestedJoin: The tables are joined on Index and NextIndex to match rows with their "next row."
- 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!