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"