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 ,
You should be able to do this using a related calendar table (for month/week/day etc.) and your data unpivoted in Power Query to create the hours dimension. Add all these into the visual drilldown hierarchy and it should give you want you need.
Are you able to share your example data in table format (use the table function in the comment area) with the [resident] and [community] fields anonymised?
Pete
I have added an additional layer of this since I wrote the question, namely the TYPE of interaction (be it Nutrition, Mobility, Communicating, Going to the Toilet, etc). I now have a 16 column table, which is more than the table function on here will allow. But, if I change the periods to 4hr instead of 2hr, I can fit. It is as follows:
Resident Community Date Type 0000-0400 0400-0800 0800-1200 1200-1600 1600-2000 2000-0000 Male 1 Ground 2020-01-01 Mobility 0 1 3 2 2 1 Male 2 Upper 2020-01-01 Communication 0 0 4 7 9 5
Female Central 2020-01-02 Toilet 0 1 1 0 2 1
Does this aid?
- BA_Pete
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