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