Graph by Hour starting at 7am and looping

[Thomas_MedOne]

I'm replicating a report that is created within Excel. In their Graph, they are showing information on a bar graph showing information by the hour.   I have the graph working fine calculating data. 

I have the column for Hour formatted like this: 

Hour = CONCATENATE(IF(HOUR(dbtable[date_requested])>12,HOUR(dbtable[date_requested])-12, HOUR(dbtable[date_requested])), Format(dbtable[date_requested], "AMPM"))

So, the values are outputted as the hour, etc. (4PM, 5PM, etc)

However, the visual then sorts the graph out of "hour" order showing the largest hour first on down to the smallest.

I would like it to sort from 7am - 6pm (not even 0-23).  I could format the Hour just as Hour(dbtable[date_requested]) and the graph will display in number or 0 -23  But I don't want that either. I would like it to display 7am - 6pm

How do I "sort" the X axis the way I want it?

[lbendlin · Accepted solution]

Let's assume we have this sample data

let
    Source = {7..18},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Hr_Nr"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Hour", each Time.ToText(#time([Hr_Nr],0,0),"h tt")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Number.RandomBetween(1,10))
in
    #"Added Custom1"

Once you load it in Power BI you can make a visual from it

Note that it is sorted by value. Let's sort it by hour instead (ascending)

which gives this:

Now, the last step is to employ the "Sort a column by another column" feature.  Let's sort hour by hr_nr.

And that's the result:

[Thomas_MedOne]

This worked great. I ended up creating a "sort" column translating 7 -> 0 through 6 -> 23  and then sorting by that. it worked great.

[Thomas_MedOne]

I like this. Would it work for a 24-hour day where it's 7am - 6am? looping over midnight? 

I thought about creating a sort column which would make 7 = 0 and increment but that works until I get to 23 and then hours 0-6 are negative numbers.  

Although, I may have something there.