Forum Discussion

thomasfmeier's avatar
thomasfmeier
Frequent Visitor
2 years ago
Solved

Transpose data table to get related data onto the same row

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 statuse...
Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    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.

jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
2 years ago

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"