Forum Discussion
pivot or transpose?
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!
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!
Thank you so much rubayatyasmin
Select your clock type column > Transform > Pivot Column > Make sure your value column is your datetime and the aggregation is set to "Don't Aggregate"
Thank you for your answer Syk
Just to understand, is there any difference between "with no aggregation" and selecting MAX or MIN instead?
