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
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
mahoneypatand what if the source of data was an XML field with a URL like this one?
https://www.globiflow.com/podiofeed.php?c=4444&a=666666&f=4444
Thank you in advance!
I don't see any data at that link. In any case, if it is structured like your example data, the approach would be the same.
Regards,
Pat
mahoneypat - again thanks for your solution, it's perfect. just one more question. If I end up with 1 line per appointment, and I have their start and end times, how can I then use it to create a heatmap? i.e. how will PowerBI know that there's X appointments happening on Mon at 1pm. I thought I could create columns for each time (e.g. 10am) and have calculations there to say if (10am is) higher or equal to start time AND (10am) is less than end time. However, that's a lot of columns... Is there another solution?
You shouldn't need to make extra columns. Typically, I do this by have a disconnected table with the time (or date) values that will go in the columns. Then you can right a measure that counts the # of events with start date/time < max date/time (from the disconnected table) and end date/time >= min date/time (from disconnected table). You can first store those max and min of the disconnected table as variables and then use them in the Filter().
Regards,
Pat