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
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
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?
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
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.