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.
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
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.
- thomasfmeier2 years agoFrequent Visitor
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?