Anonymous

Not applicable

5 years ago

Solved

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

Need Help

camargos88

5 years ago

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"

camargos88

Community Champion

5 years ago

Anonymous ,

I am sorry, I didn't get what you need, can you explain it more ? Example with data would be great.

Anonymous

Not applicable

5 years ago

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

4/1/2020 5:29

4/1/2020 5:31

468746

4/1/2020 5:58

4/1/2020 6:53

4/1/2020 5:34

4/1/2020 5:37

4/1/2020 6:53

4/1/2020 5:34

4/1/2020 5:36

4/1/2020 6:23

4/1/2020 5:34

4/1/2020 5:37

4/1/2020 6:23

4/1/2020 5:34

4/1/2020 5:40

4/1/2020 5:41

4/1/2020 5:34

4/1/2020 5:41

4/1/2020 6:53

4/1/2020 6:31

4/1/2020 6:53

468747

4/1/2020 5:56

468748

4/1/2020 6:00

4/1/2020 6:02

Forum Discussion

Convert Data Entered as Column Entries to Row Entries

Related content

Converting to Percentage

Calculation of Total Amount With Sales Entry

Convert String from Database Entries into Matrix Visual

Automate data entry in excel

Unique Count in Row convert