Here are two Excel "named ranges", which are Events1 and Events2 respectively. Starting from the 2^nd row, both can be regarded as a table with column headers and some of their headers are same.

Task: Reference names of the two "named ranges" and combine the two tables; display a field that does not exist under the current range name as empty.

Use SPL XLL to do this:

=spl("=?1.to(3,).($[Events1]|~.m(1,2,3,0,4))|?2.to(3,).($[Events2]|~.m(1,0,0,2,3))",Events1,Events2)

to(3,) function gets members from the 3^rd to the last. m()function gets multiple members according to their positions; 0 means null. ~ is the current member, and $[] represents a string.

Source:https://stackoverflow.com/questions/78253023/power-query-m-code-extract-a-list-of-tables-into-one-main-table-some-column