Forum Discussion
Cannot work out how to present table
Hi Anonymous ,
1. First, start by unpivoting your table in Power Query to create a timeBand field that you can add to the drilldown hierarchy.
In Power Query, go to New Source > Blank Query then open Advanced Editor and post the following code over the default code in there:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3MSVUwVNJRci/KL81LATKMDIwMdA0MgQjI8c1PyszJLKkEMg2AGCRkDFIDxYZKsTpQM0Dc0IKC1CJ0I5zzc3NL8zKTE0sy8/Og5oCwCRCbA7ElEJuCzXFLzQWaBNKSmldSlJiDbBLI+JD8zJzUEiSnGELZUKfEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resident = _t, Community = _t, Date = _t, Type = _t, #"0000-0400" = _t, #"0400-0800" = _t, #"0800-1200" = _t, #"1200-1600" = _t, #"1600-2000" = _t, #"2000-0000" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Resident", "Community", "Date", "Type"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "timeBand"}, {"Value", "interactions"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"interactions", Int64.Type}, {"Date", type date}}) in #"Changed Type"You can then follow the steps I took to do this.
2. Next, create a calendar table with days/months etc. that you can relate to your data. The relationship should look this this when done:
You could add the month/day fields into your main table by referencing the date column, but there's a higher chance of duplicating data that way and you should really have a calendar table in every model any way.
3. Next, set up your chart like this:
This actually shows different charts for all three of the drill levels, but the point is, you can see where to drag your fields to make this work. The month and day levels come from the calendar table, and the timeBand field comes from the data table.
Pete
I've had something of a change of heart about how to approach this. I did do the unpivot other columns and it seemed to work, ish. However, I came to look again at the data and realised that maybe the grid of 2 (or 4 in the example) hr slots wasn't the best approach. So, my data is now:
| Resident | Community | Date | Type of Interaction | Time | Duration | Detail of Interaction |
| Resident 1 | Community 1 | 01/01/2020 | Night Check | 02:23:31 | 0.01 | Check is asleep |
| Resident 1 | Community 1 | 01/01/2020 | Assist to toilet | 04:11:23 | 0.03 | Assisted to toilet and back into bed |
I'm aware that I could do better with Date and Time being separate, but that's neither here nor there right now.
What I'm after is still the same kind of table as before, and I'm unsure of two things:
- whether it's possible to still have the X axis show in 0000 - 0200, 0200-0400 etc on the visualisation
- how to convert my decimal duration into a duration of minutes. I went through a very complicated conversion from the pure minutes number in SQL, prior to loading into PBI, but when I convert my eg 0.03 (or when I was using 3) into "duration", it becomes 0:00:43:12???
Any suggestions / thoughts?
Super UserHi Anonymous ,
1. You can still use your time bands on the x axis, but you need to create the column. In Power Query, it can be done like this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcvLDYAwDATRXnLm4E/swLQSpf82sLA4jnbf3kMdf9AY56q4icA7zNCJ5xfizMBWL4IKU9okkej6jdStzHkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [time = _t]),
chgTimeType = Table.TransformColumnTypes(Source,{{"time", type time}}),
addTimeBand = Table.AddColumn(chgTimeType, "timeBand", each if Time.Hour([time]) >= 0 and Time.Hour([time]) < 2 then "0000-0200"
else if Time.Hour([time]) >= 2 and Time.Hour([time]) < 4 then "0200-0400"
else if Time.Hour([time]) >= 4 and Time.Hour([time]) < 6 then "0400-0600"
else if Time.Hour([time]) >= 6 and Time.Hour([time]) < 8 then "0600-0800"
else if Time.Hour([time]) >= 8 and Time.Hour([time]) < 10 then "0800-1000"
else if Time.Hour([time]) >= 10 and Time.Hour([time]) < 12 then "1000-1200"
else if Time.Hour([time]) >= 12 and Time.Hour([time]) < 14 then "1200-1400"
else if Time.Hour([time]) >= 14 and Time.Hour([time]) < 16 then "1400-1600"
else if Time.Hour([time]) >= 16 and Time.Hour([time]) < 18 then "1600-1800"
else if Time.Hour([time]) >= 18 and Time.Hour([time]) < 20 then "1800-2000"
else if Time.Hour([time]) >= 20 and Time.Hour([time]) < 22 then "2000-2200"
else if Time.Hour([time]) >= 22 then "2200-0000"
else "error"),
chgTimeBandType = Table.TransformColumnTypes(addTimeBand,{{"timeBand", type text}})
in
chgTimeBandType
2. Decimal times are fractions of days, so to convert your decimal hours (I presume your durations are in hours) to a duration, you will need to divide your duration by 24, before converting to duration data type.
Pete
Thanks. I went a slightly different way through creating some custom columns and conditioning formatting. I've ended up with this, which works well. I didn't follow some of what you put as this isn't my day job, but I also feel that this would work too. So I'm accepting as the solution for all your hard work (though everyone's help was appreciated).
Thanks