Transforming data with repeated date headers

First, let's see how we could handle this if we take the first four rows of the sample data. That is, start from this:

From here, we can transpose the table and promote the headers to get precisely the shape you specified.

Here's the code for this simplified example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JTEtT8HRR0lEy1Dcw0jcyMDIGso2Q2MZIbBMktimcHasTrWRobGIIFDQHYgsgNoOzQZLm5kBpDBkQG6zVyNjM0AhJygDkAojmWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Now we just need to do the same thing for each subtable. The trick is to use Table.Split to split the table into a list of subtables, transform each table in the list just like above, and then combine the list of tables back into a single table.

Here's the code for this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZGxDoMwEEN/BWVGIr5Aku5dmDtGDF34gv6/eqkqMLobGCyesCL57NbC6/Pe92F9hjFgijJJlKQsxIl4Jl4O3sYWkGboz6KqqnxwN0tR2zidf08lZQhZsSc4Hg9KVt2h8JmCFeJK/CBG9NJHIy/9+b2kr0a304O7B5cPbh9cP9z+OUA2Fywk5wI+/Zzn3gU8AHgB8ATgDcTd4Jre28C67gZ/e/sC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> " ")), 
    #"Split Transpose Combine" = Table.Combine(
        List.Transform(
            Table.Split(#"Filtered Rows", 4),
            each Table.PromoteHeaders(Table.Transpose(_))
        )
    )
in
  #"Split Transpose Combine"

Personally, I'd recommend unpivoting the Staff ID too so that your final result looks like this:

This format should be easier to work with if you plan to build a report off of it.

Extra steps applied:

#"Renamed Columns" = Table.RenameColumns(#"Split Transpose Combine",{{"Staff ID", "Date"}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Date"}, "StaffID", "Value")

I started on this before I noticed the other responses, but here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZGxDoMwEEN/BWVGIr5Aku5dmDtGDF34gv6/eqkqMLobGCyesCL57NbC6/Pe92F9hjFgijJJlKQsxIl4Jl4O3sYWkGboz6KqqnxwN0tR2zidf08lZQhZsSc4Hg9KVt2h8JmCFeJK/CBG9NJHIy/9+b2kr0a304O7B5cPbh9cP9z+OUA2Fywk5wI+/Zzn3gU8AHgB8ATgDcTd4Jre28C67gZ/e/sC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> " ")),
    Custom1 = Table.FromColumns({List.Repeat(#"Filtered Rows"[Column1], List.Count(Table.ColumnNames(#"Filtered Rows"))-1), List.Combine(List.RemoveFirstN(Table.ToColumns(#"Filtered Rows"), 1))}),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each if Text.Contains([Column2], "/") then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Column1] <> "Staff ID")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Column1", "StaffID"}, {"Column2", "Hours"}, {"Custom", "Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"StaffID", Int64.Type}, {"Hours", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Date", type date}}, "en-GB")
in
    #"Changed Type with Locale"

Pat