Forum Discussion
Convert Data Entered as Column Entries to Row Entries
Anonymous ,
Based on the Excel example, I've removed the Responder Id column.
Try this new m code:
let Source = Csv.Document(File.Contents("D:\Downloads\Book2.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Event Id] <> "")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Responder Id"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Event Id", Int64.Type}, {"Responder Type", type text}, {"Name", type text}, {"Action", type text}, {"Response Time", type datetime}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Event Id", "Action"}, {{"Rows", each Table.AddIndexColumn(_, "Index", 1,1), type table}}), #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Rows"}), #"Expanded Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows", {"Event Id", "Responder Type", "Name", "Action", "Response Time", "Index", "Res Type"}, {"Event Id", "Responder Type", "Name", "Action", "Response Time", "Index", "Res Type"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Rows", {"Event Id", "Name", "Action", "Response Time", "Index"}, "Item", "Value"), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Event Id", "Action", "Response Time", "Index", "Item", "Value"}, "Attribute", "Value.1"), #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns1", "Index", "Index - Copy"), #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Index", "Index - Copy.1"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column1", {{"Index - Copy", type text}}, "pt-BR"),{"Item", "Index - Copy"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Item"), #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Index", type text}}, "pt-BR"),{"Action", "Index"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Action.1"), #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Index - Copy.1", type text}}, "pt-BR"),{"Attribute", "Index - Copy.1"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Name"), Union = Table.Combine({ Table.RenameColumns(Table.SelectColumns(#"Merged Columns2", {"Event Id", "Response Time", "Action.1"}), {{"Response Time", "Value"}, {"Action.1", "Item"}}), Table.Distinct(Table.SelectColumns(#"Merged Columns2", {"Event Id", "Item", "Value"}), {"Event Id", "Item"}), Table.Distinct(Table.RenameColumns(Table.SelectColumns(#"Merged Columns2", {"Event Id", "Name", "Value.1"}), {{"Name", "Item"}, {"Value.1", "Value"}}), {"Event Id", "Item"}) }), #"Pivoted Column" = Table.Pivot(Union, List.Distinct(Union[Item]), "Item", "Value") in #"Pivoted Column"
Hi Anonymous ,
Check the attached file:
Thank you for the suggestion, unfortunately, this isn't exactly what I was wanting to do. I want to take the raw data as multiple row entries and convert it to a single row entry.
Any other suggestions?
Anonymous ,
I am sorry, I didn't get what you need, can you explain it more ? Example with data would be great.
Absolutely. Hope this helps.
My raw data is exported in a vertical fashion where 1 Event ID will be entered into a row followed by the Res Type, Action, and Response time. So using Event ID 468745 has the 1st entry where the Action is OnScene and Response Time then a 2nd entry when the Action is Departed and Respone Time. Instead of having mulitple rows, I'd like to automatically change the format to the 2nd table where I have 1 row entry with the corresponding Res Type, Action, Respnose Time as Colunns.
Event Id Res Type Action Response Time 468745 MA OnScene 4/1/2020 5:29 468745 MA Departed 4/1/2020 5:31 468746 TW OnScene 4/1/2020 5:58 468746 TW Departed 4/1/2020 6:53 468746 P EnRoute 4/1/2020 5:34 468746 P OnScene 4/1/2020 5:37 468746 P Departed 4/1/2020 6:53 468746 F EnRoute 4/1/2020 5:34 468746 F OnScene 4/1/2020 5:36 468746 F Departed 4/1/2020 6:23 468746 E EnRoute 4/1/2020 5:34 468746 E OnScene 4/1/2020 5:37 468746 E Departed 4/1/2020 6:23 468746 MA Notified 4/1/2020 5:34 468746 MA EnRoute 4/1/2020 5:34 468746 MA OnScene 4/1/2020 5:40 468746 MA Departed 4/1/2020 5:41 468746 MA Notified 4/1/2020 5:34 468746 MA EnRoute 4/1/2020 5:34 468746 MA OnScene 4/1/2020 5:41 468746 MA Departed 4/1/2020 6:53 468746 TW OnScene 4/1/2020 6:31 468746 TW Departed 4/1/2020 6:53 468747 MA OnScene 4/1/2020 5:56 468747 MA Departed 4/1/2020 5:56 468748 MA OnScene 4/1/2020 6:00 468748 MA Departed 4/1/2020 6:02 In this table I have the multiple row entries converted to 1 row entry with column headings representing the row entries. Using Event ID 468746 as an example, I have multiple Response Types, multiple entries of OnScene and Departed, etc. that I'm hoping to convert to this table format.
EventID Resp1Type Resp1Notified Resp1EnRoute Resp1OnScene Resp1Departed Resp2Type Resp2Notified Resp2EnRoute Resp2OnScene Resp2Departed Resp3Type Resp3Notified Resp3EnRoute Resp3OnScene Resp3Departed Resp4Type Resp4Notified Resp4EnRoute Resp4OnScene Resp4Departed Resp5Type Resp5Notified Resp5EnRoute Resp5OnScene Resp5Departed Resp6Type Resp6Notified Resp6EnRoute Resp6OnScene Resp6Departed Resp7Type Resp7Notified Resp7EnRoute Resp7OnScene Resp7Departed 468745 MA 4/1/2020 5:29 4/1/2020 5:31 468746 TW 4/1/2020 5:58 4/1/2020 6:53 P 4/1/2020 5:34 4/1/2020 5:37 4/1/2020 6:53 F 4/1/2020 5:34 4/1/2020 5:36 4/1/2020 6:23 E 4/1/2020 5:34 4/1/2020 5:37 4/1/2020 6:23 MA 4/1/2020 5:34 4/1/2020 5:34 4/1/2020 5:40 4/1/2020 5:41 MA 4/1/2020 5:34 4/1/2020 5:34 4/1/2020 5:41 4/1/2020 6:53 TW 4/1/2020 6:31 4/1/2020 6:53 468747 MA 4/1/2020 5:56 4/1/2020 5:56 468748 MA 4/1/2020 6:00 4/1/2020 6:02