Forum Discussion
Power Query/Pivot - Hours not in chronological order
Hi Masters. I am trying to create a bar graph to visualize the sum of calls we receive per every hour chronologically using Power Query. I have tried to created it on normal excel pivot and experience no issues only when i try to perform the same action on Power Query/Power Pivot.
The 'time' call in the data source is (hh:mm:ss) and sorted in the correct order. However when i pivot the time and then group every time by 'hours' the pivot displays the hours in the incorrect order. I have attached couple of screenshots to show what is happening.
Do you have a solution for this?
2 Replies
Adding data to data model and grouping by hours new column is created as text
and it is sorted as text
Just select this column in Power Pivot and format it as Whole Number
Return to Chart, now it is sorted correctly
Sorting will not be as expected if the hours stored in the columns are Text entries.
To see if the Hours are stored as Text in the Hour column, assuming your hour is in B2, place the formula =ISNUMBER(B2) in a blank cell and see what does it return?
if it returns False that means the Hours are stored as Text but not as real numbers.
How are you extracting the Hours from the Date column? Do you use a formula? If yes, what's that formula?
