Transforming data with repeated date headers

[Ja12345]

Hello. I'm having difficulty using Power Query to transform a data report that is received in the below format.

I have been trying to research similar issues for the past days but haven't had success applying them.

The data report is always received with repeating staff ID in the first column, dates as repeating headers , and the hours worked as values. The dates go 5 columns across before wrapping underneath, always with a blank row in between each chunk of data.

I need to summarise the hours individual staff ID worked on certain days/for the month, so want to put this in a better format in order to analyse.

Sample data:

Staff ID	1/02/2023	2/02/2023	3/02/2023	4/02/2023	5/02/2023
1341	7	8	6	7	8
77134	8	6	7	8	6
123612	6	7	0	2	8
Staff ID	6/02/2023	7/02/2023	8/02/2023	9/02/2023	10/02/2023
1341	0	0	0	0	0
77134	8	6	8	6	8
123612	8	8	8	8	8
Staff ID	11/02/2023	12/02/2023	13/02/2023	14/02/2023	15/02/2023
1341	8	8	8	6	0
77134	5	5	5	6	8
123612	0	0	0	8	6
Staff ID	16/02/2023	17/02/2023	18/02/2023	19/02/2023	20/02/2023
1341	8	8	6	0	0
77134	8	8	6	0	0
123612	8	8	8	0	0

Desired output:

Staff ID	1341	77134	123612
1/02/2023	7	8	6
2/02/2023	8	6	7
3/02/2023	6	7	0
4/02/2023	7	8	2
5/02/2023	8	6	8
6/02/2023	0	8	8
7/02/2023	0	6	8
8/02/2023	0	8	8
9/02/2023	0	6	8
10/02/2023	0	8	8
11/02/2023	8	5	0
12/02/2023	8	5	0
13/02/2023	8	5	0
14/02/2023	6	6	8
15/02/2023	0	8	6
16/02/2023	8	8	8
17/02/2023	8	8	8
18/02/2023	6	6	8
19/02/2023	0	0	0
20/02/2023	0	0	0

Any help would be greatly appreciated!

[AlexisOlson · Accepted solution]

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")

[ppm1 · Accepted solution]

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

[Ja12345]

Awesome, I'm so glad you still posted as this is the perfect solution for me, it lets me look through each step and see what's happening. It's also dynamic based on the amount of staff IDs in the table, which can be variable. THANK YOU!

[wdx223_Daniel]

import your data without headers, ie. the header names should be Column1, Column2,..... etc.

then try this code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.Combine(Table.Group(Source,"Column1",{"n",each Table.UnpivotOtherColumns(Table.PromoteHeaders(Table.RemoveLastN(_,each List.Contains({null,""},Text.Trim(Text.From([Column1]))))),{"Staff ID"},"Date","Value")},0,(x,y)=>Byte.From(y="Staff ID"))[n]),
    Custom2 = Table.TransformColumns(Custom1,{{"Staff ID",Text.From},{"Date",each Date.From(_,"fr")}}),
    Custom3 = Table.Pivot(Custom2,List.Distinct(Custom2[Staff ID]),"Staff ID","Value")
in
    Custom3

[AlexisOlson · Accepted solution]

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")