Stack the Diagonal from left to right of a matrix in rows

Today we are going to discuss about numerous methods in Excel and Power Query to stack the diagonal from left to right of a matrix in rows as shown below.

Excel Solution:

Method-1:

=LET(a,A12:D15,s,SEQUENCE,DROP(REDUCE("",s(ROWS(a)*2-1),LAMBDA(x,y,IFNA(VSTACK(x,TOROW(INDEX(a,y+1-s(y),s(y)),3)),""))),1))

Method-2:

=LET(a,A12:D15,r,SEQUENCE(ROWS(a)),c,TOROW(r),DROP(IFNA(REDUCE("",DROP(GROUPBY(TOCOL(r+c,,1),TOCOL(a,,1),ARRAYTOTEXT,0,0),,1),LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,", ",,1)))),""),1))

Method-3:

=LET(x,A2:B3,IFNA(DROP(REDUCE("",SEQUENCE(ROWS(x),,0),LAMBDA(a,v,HSTACK(a,LET(z,DROP(HSTACK(x,""),-v,v),VSTACK(EXPAND("",v+1,,""),TAKE(z,,1),TOCOL(DROP(TAKE(z,-1),,1),3)))))),1,1),""))

M-Code:

Method-1:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result =

 [

 Cols = Table.ToColumns(Source),

 Rows = List.Reverse(Table.ToRows(Source)),

 fin = Table.FromColumns(List.Transform({0..List.Count(Cols) - 1}, (f) => List.Repeat({null},f) & List.RemoveLastN(Cols{f},f) & List.Skip(Rows{f},f + 1)))

 ]

 [fin]

in

 Result

Method-2:

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Result =

[

Cols = Table.ToColumns(Source),

Rows = List.Reverse(Table.ToRows(Source)),

fin = Table.FromColumns(

List.Transform(

List.Positions(Cols),

(f) =>

List.Repeat({null},f) &

List.RemoveLastN(Cols{f},f + 1) &

List.Skip(Rows{f},f)

)

)

]

[fin]

in

Result