In the Excel table below, columns from the 2^nd to the 6^th has empty cells.

Task: From each column from the 2^nd to the 6^th, find the first and the last non-empty cells and concatenate the cell values they correspond in the 1^st column using "to".

Use SPL XLL to do this:

=spl("=d=E@2p(?),d.to(2,).(d(1)(~.pselect@z(~)) /""to""/ d(1)(~.pselect(~)))",A2:F15)

E@2p converts a two-layer sequence to a table sequence. pselect()function gets position of the first member that meets the specified condition; @z enables a search backwards. to(2,) gets members from the 2^nd to the last; (1) represent the 1^st member.

Source:https://stackoverflow.com/questions/78137689/power-query-find-first-and-last-non-blank-cell-in-each-column-and-reference-the