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...
  • 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.