Display line graph in 1-hour gap or interval

[Anonymous]

Hi Everyone,

I need to show the reading of intruments captured live. @BA_Pete have helped me on how to show the line graph every hour. However, I realized that not all timestamps are having the same intervals. Because of this, the filter settings I made to show the line graph every hour would suddenly lost it's visual since the regular interval of minutes and seconds have changed. I wonder how can I resolve this problem. Below is a snapshot of what I mean. This first image is having xx:00:03 (0 min and 3 sec interval). Now since the interval have changed to 1min 9sec on some records then I have lost the visual. It is also the reason why some months cannot be displayed although there are intrument reading because of the timestamp chance on interval. I simply need to display these visual in an hourly basis. If I will  not use any filter in time, there will be no issues BUT the graph will be displayed in 30sec gap which is not the way how we want to display the instrument readings. Any help? Thank you!

[Anonymous]

I have to thank you once again Pete! I can have a plot density based on every hour plus I can also have a plot density of 24-hour gap based on specific hour of the day. Do you have a webpage of a Youtube channel that I can follow? I am new in Power Bi and the solutions you have shared is actually not for beginners and I certainly would love to get to the same level as you do. Thank you once more sir!

[BA_Pete]

Hi @Anonymous ,

No problem, always happy to help 🙂

I don't have a YouTube channel myself, but I'd recommend these. Both have good production value and explain things really clearly:

- Curbal

Ruth does really good step-by-step videos on lots of topics for beginners right through to advanced techniques, both in Power Query and DAX.

- Guy in a Cube

Patrick and Adam cover some slightly more advanced topics and go further into some of the more technical aspects of Power BI/Power Query/DAX and Power Platform.

Pete

[Anonymous]

Once again, thank you Pete. I will try this following your advise and let you know if the solution worked.

[BA_Pete · Accepted solution]

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