Cleaning Messy Data

You have all the right ideas to clean this data.
Unpivoting the date columns is critical to achieving your desired goal. The issue in this spreadsheet is the lack of a usable date in each column that can be used in the unpivot process. 

There are likely a few ways to tackle cleaning this data but I was doing it I would;

1) Remove blank rows

2) Promote Headers so the Name, Client, Task Type, Department, and Comments are headers. All other columns should be Column 6 ... Column X

3) Rename the Column 6 ... Column X with the coresponding Date Value

4) Fill Down the Employee Names

5) Replace null values in the Client Columns with "Daily Total"

6) Unpivot Other Columns (selecting Name, Client, Task Type, Department and Comments as the pivot columns)

7) Change column data types to match expected

From there you should be able to create any of the visuals you need.

The magic happens in step 3 and involves creating and manipulating nested tables.

In order to get a list that can be used to rename the columns you would have to;

a) take the first row of the table (which should be nulls except for the week beginning dates)

b) convert that row to a table

c) fill down the column with the dates

d) remove the rows that still have null in the date column (should be the first 5 columns)

e) group the rows by the date column choosing all rows instead of aggregating the rows

f) you should now have a table of the week beginning dates with column containing a table for each row (nested tables)

g) you need to add an index column to each of the nested tables starting at 0 and incrementing by 1

h) using that index column, create a new date column in each of the nested tables by adding the index value of the row to the date in that row. (Date.AddDays())

i) from each of the nested tables, select the column name and new date columns

j) remove the column of grouped dates leaving you with the nested table column

k) the nested table column and change the columns to text type

l) You can now rename the main table using the table you just created leveraging the List.Zip() function

Please feel free to send a direct message if you have additional quetions.