I am trying to convert this table:
ID rating cntry Index
Albert 603 pl 0
Peter 912 at 1
Peter 907 at 2
Albert 608 pl 3
Franz 833 pl 4
Peter 894 at 5
Eddie 753 it 6
Peter 884 at 7
Peter 905 at 8
Joe 787 de 9
to a table with half the number of rows but double the number of columns by "splitting the data".
ID rating cntry Index ID 2 rating 2 cntry 2 Index 2
Albert 603 pl 0 Peter 912 at 1
Peter 907 at 2 Albert 608 pl 3
Franz 833 pl 4 Peter 894 at 5
Eddie 753 it 6 Peter 884 at 7
Peter 905 at 8 Joe 787 de 9
I tried using Pivot with a helper column but I can only get this approach work with a single column and not with three columns.
Another way.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = Table.SelectRows( Source, each Number.Mod(_[Index],2) = 0),
Custom2 = Table.AddColumn( Custom1, "r", each Table.SelectRows( Source, (x)=> x[Index] = [Index]+1) ),
Custom3 = Custom2,
#"Expanded r" = Table.ExpandTableColumn(Custom3, "r", {"ID", "rating", "cntry", "Index"}, {"ID2", "rating2", "cntry2", "Index2"})
in
#"Expanded r"