Forum Discussion
Convert Data Entered as Column Entries to Row Entries
Hi. I have been looking through the Search for a way to accomplish my desired format manipulation but I'm not sure that I'm using the appropriate terminology. Is it possible to perform the following...
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"
Anonymous ,
I don't have this option. However paste this code on Edit Queries -> New Source -> Blank Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZNPC4IwGIe/iuwsOOc2zVvQhA79wYQO4iFsgYem5Ar69q06lPpqu3WYjO3x97y6d3mOxE0q7SyPyEWpbJ3s3kgznZe6qtV7ralVK52sOktUuDmiPAopM1uruXls1K6U6vkK9XyPYIIdFpMZQC5kc7hoeeyigf9BudnK9mOhLAJIKJTHLOiiWzOESuur7mUGdAiC8iAcgnbuxNadjLr5EITdpOcWtm5h+93C2v0683Wtq1M1OHMKoFZ1jrccxQAJtxz1/1sppLfrpbHLweFr9Ds0nKyUcYCE/+k3Gk2E8hhjgIQrxQQVxQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Event Id", Int64.Type}, {"Res Type", type text}, {"Action", type text}, {"Response Time", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"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", "Res Type", "Action", "Response Time", "Index"}, {"Event Id", "Res Type", "Action", "Response Time", "Index"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Rows", {"Event Id", "Action", "Response Time", "Index"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Index", "Index - Copy"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Index - Copy", type text}}, "pt-BR"),{"Attribute", "Index - Copy"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"RespType"),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Index", type text}}, "pt-BR"),{"Action", "Index"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Action.1"),
Union =
Table.Combine({
Table.RenameColumns(Table.SelectColumns(#"Merged Columns1", {"Event Id", "Response Time", "Action.1"}), {{"Response Time", "Value"}, {"Action.1", "Item"}}),
Table.Distinct(Table.RenameColumns(Table.SelectColumns(#"Merged Columns1", {"Event Id", "RespType", "Value"}), {{"RespType", "Item"}}), {"Event Id", "Item"})
}),
#"Pivoted Column" = Table.Pivot(Union, List.Distinct(Union[Item]), "Item", "Value")
in
#"Pivoted Column"That's awesome...you are a Super User! That worked exactly how I envisioned.
Only problem, and it's not something that you provided. I want to apply it to my actual raw data file but it contains more columns than I provided. I thought I would be able to expand it but the code is more complicated than I imagined. What would the code be for this format and where in the Steps would I copy/paste it? This file has been reduced to what will end up being a single row with the corresponding entries. Here's a dropbox link to the file.
https://www.dropbox.com/s/3shkz9p481vx57r/responders2.csv?dl=0
If this is asking too much feel free to say so...I would completely understand.