Display line graph in 1-hour gap or interval

Hi Anonymous ,

I think the easiest way to go would be to group on date and hour and pick out the row that has the lowest timestamp. HOWEVER, note that this method will break query folding.

1) Create a new custom column for date:

// Timestamp date
Date.From([TimeStamp])

2) Create another column for hour:

// Timestamp hour
Time.Hour([TimeStamp])

3) Multi-select your new colums and go to Home tab > Group By. Create an aggregate column called 'data' an use the 'All Rows' operation.

4) Create another custom column to pick out the rows in the nested tables that have the lowest timestamp:

// Min timestamp
Table.Min([data], "TimeStamp")

5) Expand the resulting record, selecting which columns you want to bring back.

Here's an example of the grouping/get min/expanding steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcwxEoAwCATAv1BbAJIxXqc+I5P/f8MQES2Ogp271uigZabwOMyQAhbqyyPniIYoY9WUy58BBq0Tzhh7K7aibCn/MTOIpviYfaJP54q1D0wTvLZPEXCF1RSPvLKDjXq/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [resource = _t, category = _t, hours = _t, value = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"resource", type text}, {"category", type text}, {"hours", Int64.Type}, {"value", type time}}),
    groupRows = Table.Group(chgTypes, {"category"}, {{"data", each _, type table [resource=nullable text, category=nullable text, hours=nullable number, Tarih.2=nullable text, Index=number, Index.1=number, Tarih.2.1=nullable text, Custom=nullable logical]}}),
    addMinValue = Table.AddColumn(groupRows, "minValue", each Table.Min([data], "value")),
    expandMinValue = Table.ExpandRecordColumn(addMinValue, "minValue", {"hours", "value"}, {"hours", "value"}),
    remDataCol = Table.RemoveColumns(expandMinValue,{"data"})
in
    remDataCol

 Pete