Forum Discussion
Cannot work out how to present table
I have the following data:
It shows for a person, in a community and on a day by day basis, the number of 'interactions' with that person during a 24hr period. Ideally, I would like to create a column chart that shows this data more visually, but I just can't get my head around how best to present it. I need to have the 24hr period (or the 12 blocks) on the X axis, and the Y axis to show the number of occurences per day, month, and individual person. Is this something that can be done easily? Something akin to this, but on one level showing for Y to show for the month, then the day, then the average for that individual across the 24hr period:
Any assistance offered would be appreciated.
Thanks
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
7 Replies
Hi 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?
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
The first step in your query will be to highlight your Resident, Community and Date columns (control click on all three). Right click once selected and choose Unpivot Other columns. This will result in two new columns (time block and # of interactions). You can then create a measure that sums your interactions column and use it in a visual with the time block column as the X axis.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
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?
Hi 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 chgTimeBandType2. 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
