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.
Community ChampionHi raymondwkmok, I've created for you a function where you can choose:
- 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.
- 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]
