Creating Daily Profile Graphs from Interval Data

[Anonymous]

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

[Anonymous]

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. 

[BA_Pete]

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

[Anonymous]

Thanks will give this a shout and get back to you. Currently I just have day 1, day 2 etc as you can see in my table but happy to change this in excel before bringing over to BI.

[BA_Pete · Accepted solution]

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
    unpivOthCols

Example output:

Pete

[Anonymous]

Hi @BA_Pete ,

Strugglign with this I am afraid. I have switched to data from report added a new column titled Date. Do formulas work similarly to excel in Power BI? Can I use just an If function for instance. I can't really follow your description above. 

[BA_Pete]

No problem.

DAX formulas work the same as in Excel (same language). These are created in the report side of Power BI and are usually things like SUM(yourTable[Sales]) or AVERAGE(yourTable[Age]) etc.

Power Query is where you want to do the structural/transformational parts, such as adding new columns etc. Power Query works in M language and this is what the examples I gave you were - code for adding new columns in Power Query.

For example, to add a column in Power Query telling you whether your date is weekday/weekend, you would select your table in Power Query, go to the Add Column Tab > Custom Column, then just paste the following into the calculation area (adjusting to match your actual [Date] column name if different):

if List.Contains({5, 6}, Date.DayOfWeek([Date], Day.Monday)) then "Weekend"
else "Weekday"

If you want to see all of the items we discussed working, you can go into Power Query, Home tab > New Source > Blank Query. Then select this blank query and go to Home tab > Advanced Editor, then paste all of this over the default code in there:

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
    unpivOthCols

Apologies if I've misunderstood your post and I'm teaching you to suck eggs here 🙂

Pete

[Anonymous]

Hi @BA_Pete finally got round to giving this a go. The transform worked and I have the graph set up with my slicers etc but the time along the x axis is goign from high to low rather than in normal order? Any ideas? 

[BA_Pete]

Ok. As you've added "hr" to all the time values they are now text values, so may not sort as required.

First thing to try is click the ellipsis at the top-right of your chart visual and find the 'Sort Axis' option there. Change the sort method from measure value to axis value ascending. This may give you what you need nice and easily.

If not, then you'll need to add a sortkey into your table. The simplest way to do this would probably be to duplicate your half hour column in Power Query before you add the "hr" onto it, and set the data type to Whole Number (or Decimal Number if you're folding your query). Once you have a numerical equivalent of your values and apply the change to the model, you can select your text version column from the Fields list, then hit 'Sort By Column' in the Column Tools tab to set the numerical version as the sortkey.

Pete

[Anonymous]

Thanks @BA_Pete  The ellipsis gave me the option to switch to time. This worked👍 Now for trying to get the weekends and weekdays seperated will let you knwo how I get on. 

[BA_Pete]

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

[Anonymous]

Hi Pete, 

Trying this first step and it is not allowing me to select pivot column. Any ideas? 

[BA_Pete]

You don't want to pivot, you want to select the Unpivot Columns dropdown, then select Unpivot Other Columns.

Pete