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