Forum Discussion
Creating Daily Profile Graphs from Interval Data
Good afternoon guys,
I was wondering whether Power Bi has the capability to deal with interval data in the form of 1 day per row such as the below. I have electricity and gas usage data and I wanted to easily make daily profile graphs using Power BI but am unsure as to whetehr Power BI can deal with data in this form. Any help is much appreciated. See below for a small example of my data.
| Site Name | MPR | Day | 0000 | 0030 | 0100 | 0130 |
| Centenary Court | 8819570205 | 1 | 0 | 10 | 0 | 0 |
| Centenary Court | 8819570205 | 2 | 15 | 0 | 20 | 0 |
| Centenary Court | 8819570205 | 3 | 0 | 30 | 0 | 0 |
No worries, let me know how you get on.
I would add a calculated date in Power Query when you do the other transformations if I were you. It will be (to my mind) far easier, and keep all data transformations in the same place.
To get a date in PQ from the 'Day 1/2' format, you just need to know your fixed starting date (the date of Day1) then add a custom column like this, assuming 'Day1' is 1st Jan 2023:
Date.AddDays(#date(2022, 12, 31), [Day])*EDIT* Here's a full example query applying all the things we've discussed so far:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck7NK0nNSyyqVHDOLy0qUdJRsrAwtDQ1NzAyMAVyDIHYAEQbQBkGSrE6BHUZgXSYQnUYEa3NGKrDGMmyWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Site Name" = _t, MPR = _t, Day = _t, #"0000" = _t, #"0030" = _t, #"0100" = _t, #"0130" = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Site Name", type text}, {"MPR", Int64.Type}, {"Day", Int64.Type}, {"0000", Int64.Type}, {"0030", Int64.Type}, {"0100", Int64.Type}, {"0130", Int64.Type}}), addReadDate = Table.AddColumn(chgTypes, "readDate", each Date.AddDays(#date(2022, 12, 31), [Day])), addDayType = Table.AddColumn(addReadDate, "dayType", each if List.Contains({5, 6}, Date.DayOfWeek([readDate], Day.Monday)) then "Weekend" else "Weekday"), unpivOthCols = Table.UnpivotOtherColumns(addDayType, {"Site Name", "MPR", "Day", "readDate", "dayType"}, "halfHour", "value") in unpivOthColsExample output:
Pete
12 Replies
Hi Anonymous ,
It can technically deal with data in this format but it would be a PITA to write measures for it (you would need to write one per half-hour column). It would also be incredibly space-inefficient when compressed using the VertiPaq engine.
The 'correct' way to handle this in PBI (Power Query) would be to multi-select the [Site Name], [MPR], and [Day] columns, go to the Transform tab > Unpivot Columns > Unpivot Other Columns. This gives you an easily compressable and 'normalised' data structure to report from, and measures can be written over a single [Value] column.
Pete
You don't want to pivot, you want to select the Unpivot Columns dropdown, then select Unpivot Other Columns.
Pete
Thanks Pete, appreciate the reply. Will give this a go.
Another query I have is whether Power BI will be able to differenctiate dates into weekends and weekdays? Basically what I am trying to achieve is 2 line graphs on one chart with the average weekend daily profile and average weekday daily profile.
It can, given proper dates to work with. I didn't see any in your example data, but I assume they exist in there somewhere.
If you want to keep it simple, then you can just add a custom column to your main data table, something like this:
if List.Contains({5, 6}, Date.DayOfWeek([Date], Day.Monday)) then "Weekend" else "Weekday"If you go with this metho, then I'd recommend applying it BEFORE you do your table unpivot. This will be more efficient than doing it after.
Pete