Forum Discussion
pivot or transpose?
Hello everyone
I have this table on the left and I want to have the table on the right
Can you please help me on how to do this?
Thanks in advance
sure. follow the image.
1. open power query
3. select any column except CLOCK_DATETIME (make sure it is date time type)
2. transform > pivot >>pivot on CLOCK_DATETIME
expand advanced option. select do not aggregate.
do not aggregate will keep your value same, whereas aggregating value will for example count will only count different dates. In this case value will be 1. as you have one date only. you can open power query and play around with the agg functions to better understand. Let me know if you need to solution file.
and voila!
7 Replies
Thank you for your answer rubayatyasmin but can you be more specific? It's not working
this is what I got
= Table.Pivot(Table.TransformColumnTypes(dbo_PCC_ClockInOut, {{"CLOCK_TYPE", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(dbo_PCC_ClockInOut, {{"CLOCK_TYPE", type text}}, "en-GB")[CLOCK_TYPE]), "CLOCK_TYPE", "CLOCK_DATETIME", List.Count)
sure. follow the image.
1. open power query
3. select any column except CLOCK_DATETIME (make sure it is date time type)
2. transform > pivot >>pivot on CLOCK_DATETIME
expand advanced option. select do not aggregate.
do not aggregate will keep your value same, whereas aggregating value will for example count will only count different dates. In this case value will be 1. as you have one date only. you can open power query and play around with the agg functions to better understand. Let me know if you need to solution file.
and voila!