Forum Discussion

sfung's avatar
sfung
New Member
6 years ago
Solved

Manually moving rows in a table

Hi, I'm reporting data in a table format. I have sorted rows in order by columns but need to manually move one row..   Is there a way to do this?   Thanks
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    waiting foe MS to develop what you nedde, give a try to this  function:

     

    let
    moveRow = (tab,row, posToMove)=>
    let 
    Pos=Table.PositionOf(tab,row),
    fromPos=Table.RemoveMatchingRows(tab,{row}),
    toPos=Table.InsertRows(fromPos,Pos+posToMove,{row})
    
    in 
        toPos
in
    moveRow

     

    if you have a table like this and want to move row [col1=22,col2=bb] two position down

     

     

    you can call the function and get this

     

     

    if you want to move row [col1=33,col2=cc] two positio up (use a negative relative position)

     

     

    it is possible using this scheme to add also the management of moving whatever row from pos Pi to position Pf

     

     

edhans's avatar
edhans
Community Champion
6 years ago

No. You cannot manually move a row. Rows in Power BI (DAX or Power query) aren't like rows in Excel that have numbers and you can move them around.

 

You'd need to add an index that had some logic to know that what you perceive is on row 14 you want to move it above what is on the row above it, and then sort by that index.

 

The only way to attempt to do this that I can conceive of would be a hassle, but would be along the lines of this - Assume you want row 13 and 14 to switch places

  1. Use Table.RowCount to get total rows
  2. use Table.FirstN to get rows 1-12
  3. Use Table.LastN to get all rows below 14 using the count of #1 above less 14
  4. Use Table.Skip to remove table rows 1-13 of the orginal table, then Table.First to keep that row as row 14
  5. Use Table.Skip to remove table rows 1-12 of the ororiginal table, then table.first to keep that row as row 13
  6. Create a new table with Table.Combine, and combine table #2, 4, 5, then 3

If you don't know the row numbers, then it gets more complex as you have to use List.PositionOf to find what you are looking for and what row it is on. It will get real tedious as you can imagine, but it is doable.

 

Then you need to add an index, because none of the row orders matter to Power BI visuals. They sort data.