Change data input from column to row - best practice, Excel, SQL or PowerBI?

[bilingual]

Hi, my data is in SAP and currently designed by rows and i can not change this, but i need to change some parts of it into columns.

Here is an example:

Present structure

ID	Stop	Date	Time
P6544	2	01-01-2021	10:50
P6544	4	01-01-2021	11:55
P6544	4	02-01-2021	11:35
P6585	4	01-01-2021	09:45

Here is what i would like the structure to be changed into:

ID	Stop 1	Date 1	Time 1	Stop 2	Date 2	Time 2	Stop 3	Date 3
P6544	2	01-01-2021	10:50	4	01-01-2021	11:55	4	02-01-2021
P6585	4	01-01-2021	09:45

So some tips regarding changing structure from column to row would be well recieved 🙂

And sorry about the messed up tables, i use the tabletool from PowerBI and it still gives me a html error...

[amitchandak · Accepted solution]

@bilingual , first add an index column by ID -https://www.youtube.com/watch?v=7CqXdSEN2k4

then unpivot new index column and date

https://radacad.com/pivot-and-unpivot-with-power-bi

[v-lionel-msft · Accepted solution]

Hi @bilingual ,

Maybe you can refer to my .pbix file.

Best regards,
Lionel Chen

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

[amitchandak · Accepted solution]

@bilingual , first add an index column by ID -https://www.youtube.com/watch?v=7CqXdSEN2k4

then unpivot new index column and date

https://radacad.com/pivot-and-unpivot-with-power-bi