Pivot Columns with repeating rows

[swmakin]

I am trying to pivot columns with repeating data to rows. However as I have duplicated information in the Data column when I Pivot (Don't aggregate) I get the following error message.

Value Data Name Eric Address Line 1 29 Acacia Road Address Line 2 Dandytown Address Line 3 Beano Address Line 4 SE10 1BA Address Line 5 0 Address Line 6 0 Name General Blight Address Line 1 29 Acacia Road Address Line 2 Dandytown Address Line 3 Beano Address Line 4 SE10 1BA Address Line 5 0 Address Line 6 0 Name Chief O'Reilly Address Line 1 29 Acacia Road Address Line 2 Dandytown Address Line 3 Beano Address Line 4 SE10 1BA Address Line 5 0 Address Line 6 0 <tbody> </tbody>

<tbody>
</tbody>

Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
List

Help much appreciated.

 

[gazpage]

let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    AddedCustom = Table.AddColumn(#"Added Index", "Address Line 1", each #"Added Index"{[Index]+1}[Data]),
    AddedCustom2 = Table.AddColumn(AddedCustom, "Address Line 2", each #"Added Index"{[Index]+2}[Data]),
    AddedCustom3 = Table.AddColumn(AddedCustom2, "Address Line 3", each #"Added Index"{[Index]+3}[Data]),
    AddedCustom4 = Table.AddColumn(AddedCustom3, "Address Line 4", each #"Added Index"{[Index]+4}[Data]),
    AddedCustom5 = Table.AddColumn(AddedCustom4, "Address Line 5", each #"Added Index"{[Index]+5}[Data]),
    AddedCustom6 = Table.AddColumn(AddedCustom5, "Address Line 6", each #"Added Index"{[Index]+6}[Data]),
    #"Renamed Columns" = Table.RenameColumns(AddedCustom6,{{"Data", "Name"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index"}),
    #"Removed Alternate Rows" = Table.AlternateRows(#"Removed Columns",1,6,1)
in
    #"Removed Alternate Rows"

 

[swmakin]

Many thanks, perfect!