Forum Discussion
Sort by column not working
Hi,
I am trying to create a visual that shows the time a person is called out to a job out of hours.
I can get it to show with an inverted normal distribution, where the x axis runs from 00:00 to 23:59
I would prefer the axis to run from 18:00 - 06:00 to show this data in a normal distribution in the middle of the plot. (not wrapping around each end of the visual )
I thought i could do this by creating an index table (below), creating a relationship between the times and sorting the time by the index. But when i click on the time column and say 'sort by index', nothing changes.
If possible i would also like to group the data into 30 min slots to make it look a bit less busy but havent worked that bit out yet.
Any ideas why my data isnt sorting? any help would be really appreciated!
Hi sdavidson ,
According to your statement, I think you are using "Continuous" in X-aixs Type. I think you can select “Categorical” in X-axis Type.
However this will expand your X-axis in your visual and show all time. Result is as below.
You can add a new Timegroup column and New Index for this group in Power Query Editor.
New Time Table:
let Source = List.Times(#time(18, 0, 0), 1440, #duration(0, 0, 1, 0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Time"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index for Time", 1, 1, Int64.Type), #"Inserted Hour" = Table.AddColumn(#"Added Index", "TimeGroup", each #time( Time.Hour([Time]),0,0)), #"Grouped Rows" = Table.Group(#"Inserted Hour", {"TimeGroup"}, {{"Rows", each _, type table [Time=nullable time, Index for Time=number, TimeGroup=time]}}), #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index for TimeGroup", 1, 1, Int64.Type), #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index1", "Rows", {"Time", "Index for Time"}, {"Rows.Time", "Rows.Index for Time"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Rows",{"TimeGroup", "Index for TimeGroup", "Rows.Time", "Rows.Index for Time"}), #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Rows.Time", "Time"}, {"Rows.Index for Time", "Index for Time"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Index for Time", Int64.Type}, {"TimeGroup", type time}}) in #"Changed Type1"Sort TimeGroup by Index for TimeGroup and create a the visual. Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi sdavidson ,
According to your statement, I think you are using "Continuous" in X-aixs Type. I think you can select “Categorical” in X-axis Type.
However this will expand your X-axis in your visual and show all time. Result is as below.
You can add a new Timegroup column and New Index for this group in Power Query Editor.
New Time Table:
let Source = List.Times(#time(18, 0, 0), 1440, #duration(0, 0, 1, 0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Time"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index for Time", 1, 1, Int64.Type), #"Inserted Hour" = Table.AddColumn(#"Added Index", "TimeGroup", each #time( Time.Hour([Time]),0,0)), #"Grouped Rows" = Table.Group(#"Inserted Hour", {"TimeGroup"}, {{"Rows", each _, type table [Time=nullable time, Index for Time=number, TimeGroup=time]}}), #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index for TimeGroup", 1, 1, Int64.Type), #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index1", "Rows", {"Time", "Index for Time"}, {"Rows.Time", "Rows.Index for Time"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Rows",{"TimeGroup", "Index for TimeGroup", "Rows.Time", "Rows.Index for Time"}), #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Rows.Time", "Time"}, {"Rows.Index for Time", "Index for Time"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Index for Time", Int64.Type}, {"TimeGroup", type time}}) in #"Changed Type1"Sort TimeGroup by Index for TimeGroup and create a the visual. Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks! Switching to categorical has sorted the x axis issue.
Sorry im pretty new to BI, where do i enter the code for the time group table?
That should work if your column is a true time column and does not have a seconds component.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
