Manually moving rows in a table

[sfung]

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

[sfung]

Hi all, many thanks for all your suggestions. As I have fairly basic knowledge of Power BI the easiest option is to include an additional index card which I can use to sort in the required order. It's not the most efficient option but probably one I can understand and gets me to where I need to be.

Thanks again!

Steve

[edhans]

Sometimes @sfung you should absolutely do what you understand best as it will make your life easier down the road as you have to modify it.

If you could, give kudos (the thumbs up) to anyone who assisted in this thread, and mark your solution as the final answer so this thread can marked as solved.

Please post back with any further questions on this or any other project!

[Anonymous · Accepted solution]

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

[Anonymous]

where do i put the code that defines the 'MoveRow' function? Im wanting to put the code on an appended query

[Anonymous]

or if you like more to play with GUI (and there aren't to many rows):

1.  transpose you table;
2. move you row that now is a column in the desidered position,
3. transpose back you table

PS

if you want to keep the original column names you must, always remaining in the GUI environment, transform the headers  in the first row and then at the end promote back the first row to headers

[ziying35]

Hi, @sfung 

My understanding is that your data needs to be sorted in a particular order, right? Or do you want to keep one row of your data in a fixed position, so that it doesn't participate in sorting. If so, then it can be done, Can you upload a file that has been cleared of sensitive data to cloud disk and then share the link here

[dax]

Hi @sfung ,

You could use power query to delete row, if possible could you please inform me more detailed information(such as your expected output, your sample data and your logic )? Then I will try to test this in my environment.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

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.

[Ajinkya369]

Hi @sfung ,

Show me an example to get a proper clarification of your problem.

Thank you