Forum Discussion
Heatmap ideas?
I think your example data had a copy/paste error; the duration values are not consistently in the expected place. I tried to correct it but not sure if correct. In any case, please put the M code below into a blank query to see the corrections, but more importantly, one way to transform this dataset for easier analysis/visualizaton. Once all the right types of values are consistently in each column, you can easily make a Matrix visual look like a heatmap with conditional formatting.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUTIyMDLQNTDTNTAEcsDY0MrAFMZG40OQAS4Ki3KsakkwFqzUwMoAxDWmngsMcKjHqZhOKFYHGD2Jeamj0YPNIQMeO7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, #"Date effecti" = _t, #"Mon1-carer" = _t, #"Mon1-start" = _t, #"Mon1-durat" = _t, #"Mon2-carer" = _t, #"Mon2-start" = _t, #"Mon2-durat" = _t, #"Mon3-carer" = _t, #"Mon3-start" = _t, #"Mon3-durat" = _t, #"Mon4-carer" = _t, #"Mon4-start" = _t, #"Mon4-durat" = _t, #"Mon5-carer" = _t, #"Mon5-start" = _t, #"Mon5-durat" = _t, #"Tue1-carer" = _t, #"Tue1-start" = _t, #"Tue1-durati" = _t, #"Tue2-carer" = _t, #"Tue2-start" = _t, #"Tue2-durati" = _t, #"Tue3-carer" = _t, #"Tue3-start" = _t, #"Tue3-durati" = _t, #"Tue4-carer" = _t, #"Tue4-start" = _t, #"Tue4-durati" = _t, #"Tue5-carer" = _t, #"Tue5-start" = _t, #"Tue5-durati" = _t, #"Wed1-carer" = _t, #"Wed1-start" = _t, #"Wed1-durat" = _t, #"Wed2-carer" = _t, #"Wed2-start" = _t, #"Wed2-durat" = _t, #"Wed3-carer" = _t, #"Wed3-start" = _t, #"Wed3-durat" = _t, #"Wed4-carer" = _t, #"Wed4-start" = _t, #"Wed4-durat" = _t, #"Wed5-carer" = _t, #"Wed5-start" = _t, #"Wed5-durat" = _t, #"Thu1-carer" = _t, #"Thu1-start" = _t, #"Thu1-durati" = _t, #"Thu2-carer" = _t, #"Thu2-start" = _t, #"Thu2-durati" = _t, #"Thu3-carer" = _t, #"Thu3-start" = _t, #"Thu3-durati" = _t, #"Thu4-carer" = _t, #"Thu4-start" = _t, #"Thu4-durati" = _t, #"Thu5-carer" = _t, #"Thu5-start" = _t, #"Thu5-durati" = _t, #"Fri1-carer" = _t, #"Fri1-start" = _t, #"Fri1-duratio" = _t, #"Fri2-carer" = _t, #"Fri2-start" = _t, #"Fri2-duratio" = _t, #"Fri3-carer" = _t, #"Fri3-start" = _t, #"Fri3-duratio" = _t, #"Fri4-carer" = _t, #"Fri4-start" = _t, #"Fri4-duratio" = _t, #"Fri5-carer" = _t, #"Fri5-start" = _t, #"Fri5-duratio" = _t, #"Sat1-carer" = _t, #"Sat1-start" = _t, #"Sat1-durati" = _t, #"Sat2-carer" = _t, #"Sat2-start" = _t, #"Sat2-durati" = _t, #"Sat3-carer" = _t, #"Sat3-start" = _t, #"Sat3-durati" = _t, #"Sat4-carer" = _t, #"Sat4-start" = _t, #"Sat4-durati" = _t, #"Sat5-carer" = _t, #"Sat5-start" = _t, #"Sat5-durati" = _t, #"Sun1-carer" = _t, #"Sun1-start" = _t, #"Sun1-durati" = _t, #"Sun2-carer" = _t, #"Sun2-start" = _t, #"Sun2-durati" = _t, #"Sun3-carer" = _t, #"Sun3-start" = _t, #"Sun3-durati" = _t, #"Sun4-carer" = _t, #"Sun4-start" = _t, #"Sun4-durati" = _t, #"Sun5-carer" = _t, #"Sun5-start" = _t, #"Sun5-duration" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Client", "Date effecti"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "Attribute.1", Splitter.SplitTextByPositions({0, 3}, false), {"Attribute.1.1", "Attribute.1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1.1", type text}, {"Attribute.1.2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Attribute.1.1", "Day"}, {"Attribute.1.2", "Appointment"}}),
#"Extracted First Characters" = Table.TransformColumns(#"Renamed Columns", {{"Attribute.2", each Text.Start(_, 5), type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Extracted First Characters","carer","staff",Replacer.ReplaceText,{"Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.2]), "Attribute.2", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"start", type time}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"start", null}})
in
#"Replaced Errors"You can use your columns as rows/column in the Matrix, and calculate with the duration or staff columns, and use conditional formatting to get the heatmap look. Please see this link for more details on how to do that.
https://www.youtube.com/watch?v=aCq9Nz8K_Cs
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
mahoneypat - I think I'm pretty much there. thank you for all your guidance! one last thing is whether you think it's possible to summarise somehow the below table (they show the measure we created together earlier per "shift" and per day) so that we only have the lowest value for each shift (AM and PM) for each day:
Not totally sure what you mean. Can you explain/show what your new visual would look like? You want to know the earliest appointment in the AM and in the PM for each day? Or each client?
Regards,
Pat
mahoneypat - no, I would like to know the lowest value for the measure we've created per day, per shift. For the example, the large detailed table is at the bottom, and the summarised table I'd like to get would like like this:
Day/Shift AM PM Mon -11 -7 Tue -8 -6 Wed -8 -5 Thu -9 -5 Fri -11 -7 Sat -10 -6 Sun -9 -4 Please try this expression
Min Value = MINX(VALUES('time'[Time]), [time-heatmap])
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat