Multiple columns of Data, to convert to 2 columns

[Irldigi]

Hi,

Very difficult to put a title on this.....


Name Age Name Age Name Age Name Age Name Age Name Age
John 8 Mary 5 Ian 10 Nigel 4 Helen 5 Luke 6
Paul 6 Ciara 6 Mandy 11 Anne 4 Jorden 4 Stacy 4
Pat 5 Lucy 3 Cliff 5 Jack 3
Andy 3 Mark J 7 Kevin 7

convert to this....

Name Age
John 8
Paul 6
Pat 5
Andy 3
Mary 5
Ciara 6
Lucy 4
etc etc

Thanks,

Darren

[Irldigi]

Names and Ages are obviously already in their own columns....

[alansidman]

Using Power Query, here is the Mcode. It is a case of unpivoting the rows into columns and manipulating the columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedDataType = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Name2", type text}, {"Age3", Int64.Type}, {"Name4", type text}, {"Age5", Int64.Type}, {"Name6", type text}, {"Age7", Int64.Type}, {"Name8", type text}, {"Age9", Int64.Type}, {"Name10", type text}, {"Age11", Int64.Type}}),
    UnpivotedColumns = Table.UnpivotOtherColumns(ChangedDataType, {}, "Attribute", "Value"),
    AddedCustomColumn = Table.AddColumn(UnpivotedColumns, "Age", each Number.ToText([Value])),
    ReplacedErrors = Table.ReplaceErrorValues(AddedCustomColumn, {{"Age", null}}),
    FilledUp = Table.FillUp(ReplacedErrors,{"Age"}),
    SortedRows = Table.Sort(FilledUp,{{"Value", Order.Ascending}}),
    RemovedUneededRows = Table.Skip(SortedRows,18),
    RemovedUneededColumns = Table.RemoveColumns(RemovedUneededRows,{"Attribute"})
in
    RemovedUneededColumns