Forum Discussion

Ja12345's avatar
Ja12345
Regular Visitor
3 years ago
Solved

Transforming data with repeated date headers

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 suc...
  • AlexisOlson's avatar
    3 years ago

    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's avatar
    3 years ago

    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