Forum Discussion
Transpose data table to get related data onto the same row
- 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.
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.
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?