Forum Discussion
Creating Daily Profile Graphs from Interval Data
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
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
Super User
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
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
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