Transpose data table to get related data onto the same row

[thomasfmeier]

I have the following data table. We're managing jobs (Job No). Each job undergoes different workflow status as indicated in the last column "...Message".

I'm particularly interested in the 3 statuses marked yellow.

What I need to do (hopefully with the help of Power Query as part of the data load) is to transpose this data to the following format.

So I'm creating 3 new columns and add the corresponding timestamps to it for further processing.

I can get as far as the following screenshot but I'm not able to get this data to the same row. How can I do this?

[thomasfmeier]

Yes that Finish is what we're after but I'm getting an error when trying to integrate your code. These are the initial steps I'm making to get the data ready:

let
    Source = Csv.Document(File.Contents("<path>Staff_Log_Report_reportTable.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Logged Time", type datetime}, {"Employee", type text}, {"ID", type text}, {"Message", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "ID", Splitter.SplitTextByRepeatedLengths(8), {"ID.1", "ID.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"ID.1", type text}, {"ID.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"ID.2", "Job No"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"ID.1"})
in
    #"Removed Columns"

This is a bit over my head tbh as I'm usually not in the code editor

[jgeddes · Accepted solution]

Here is your code with the required lines added...

let
    Source = Csv.Document(File.Contents("<path>Staff_Log_Report_reportTable.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Logged Time", type datetime}, {"Employee", type text}, {"ID", type text}, {"Message", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "ID", Splitter.SplitTextByRepeatedLengths(8), {"ID.1", "ID.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"ID.1", type text}, {"ID.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"ID.2", "Job No"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"ID.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Message] = "Created Job" or [Message] = "Job status set to Connect: Onsite" or [Message] = "Job status set to Job: Completed")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Job No"}, {{"_nestedTable", each Table.SelectColumns(_, {"Message", "Logged Time"}), type table [Job No=nullable number, Logged Time=nullable datetime, Message=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.PromoteHeaders(Table.Transpose(_))}}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"Job status set to Connect: Onsite", "Job status set to Job: Completed", "Created Job"}, {"Job status set to Connect: Onsite", "Job status set to Job: Completed", "Created Job"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Expanded _nestedTable",{{"Job status set to Connect: Onsite", type datetime}, {"Job status set to Job: Completed", type datetime}, {"Created Job", type datetime}})
in
    #"Changed Type3"

Somethings to note here. 

Add the path to your file back inplace of <path>.

In the #"Filtered Rows" step the text of the values chosen must exactly match the values as they exist in your table. E.g., My code - "Job status set to Job: Completed". If your data has a space after Job and before the colon then you will have to adjust the text in the code to match.

The values in the #"Expanded _nestedTable" and #"Changed Type3" steps must exactly match the #"Filtered Rows" step.

Hope this helps.

[thomasfmeier]

Yes, that worked. Thanks so much. Impressive what can be done with Power Query 🙂

Would I have been able to set this up with the editor or is this a case of writing code only like what you've done?

[jgeddes]

One way to do this would be to filter the query to include the three messages you want columns for.

Group the rows by Job Number and set the aggregate to include all rows.

Edit the grouping code to select only the message and datetime columns.

Transpose the nested tables and promote the resulting first rows as headers.

Expand the nested tables.
Example:

Start

Finish

Code used:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY27DoJAEEV/ZbI1CfuMy7R0Nn4AoUCdxAfuEnYo/HvHhE6wPTn33K5TTdS2UZUytjahtto6MA6dFnTMZyg88FKgEANnaHNKdGGEUyp3JtVX2wGPxmwGhKBUXtNITNefvV/3GvVBUDvTINp3tXsVMHhBjyU9dx2DPgrKfKMZ/pk6og+C5Hcc36vafwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Number" = _t, DateTime = _t, Message = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Number", Int64.Type}, {"DateTime", type datetime}, {"Message", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Message] = "Created Job" or [Message] = "Job status set to Connect: Onsite" or [Message] = "Job status set to Job: Completed")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Job Number"}, {{"_nestedTable", each Table.SelectColumns(_, {"Message", "DateTime"}), type table [Job Number=nullable number, DateTime=nullable datetime, Message=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.PromoteHeaders(Table.Transpose(_))}}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"Job status set to Connect: Onsite", "Job status set to Job: Completed", "Created Job"}, {"Job status set to Connect: Onsite", "Job status set to Job: Completed", "Created Job"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded _nestedTable",{{"Job status set to Connect: Onsite", type datetime}, {"Job status set to Job: Completed", type datetime}, {"Created Job", type datetime}})
in
    #"Changed Type1"