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