Power Query / M Code, extract a list of tables into one main table, some column headers same but some different and in different order (and in row 2)

Question

I have some named ranges in an excel file which I need to merge. The column names vary but most have DATE and Code. I cannot put them as tables in excel, they need to remain as a named range as I don't want to risk excel filling columns down with formulas. The problem is the headers need to be extracted as technically it's the second row.

Anyhow, I have got as far as expanding the tables but I've not been able to align the columns from the different tables. To explain below is where I've got to (commas are the delimeters)

Step: #"Recorder Columns":

name, content
Events1, table
Events2, table

Table for Events1 consists of this data:

Column1, Column2, Column3, Column4
DATE, FIRST NAME, SURNAME, CODE
1/2/24, John, Smith, 3

Table for Events2 consists of this data:

Column1, Column2, Column3
DATE, FULL NAME, CODE
1/3/24, Peter Smith, 2

I want to merge both tables. I need to ignore the first row as the headers are the second row. The end result needs to look like the below.

Name, DATE, FIRST NAME, SURNAME, FULL NAME, CODE Events1, 1/2/24, John, Smith, null, null, 3 Events2, 1/3/24, null, null, Peter Smith, 2

In reality there are many more tables, all headers are guarantied row 2 and they will have DATE, CODE but all the other fields may vary and the order may be different

This is the code so far which expands all the data from the tables but I've failed to work out how to get the headers and then merge / align them all.

let
    Source = Excel.CurrentWorkbook(),
    #"Merged Queries" = Table.NestedJoin(Source, {"Name"}, Events_List, {"NamedRanges"}, "Events_List", JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries", {"Events_List"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns", {"Name", "Content"}),

    // Expand the "Content" column to reveal the tables
    ExpandContent = Table.ExpandTableColumn(#"Reordered Columns", "Content", Table.ColumnNames(#"Reordered Columns"[Content]{0}))
in
    ExpandContent

That delivers:

Name, Column1, Column2, Column3, Column4
Events1, DATE, FIRST NAME, SURNAME, Code
Events1, 1/2/24, John, Smith, 3
Events2, DATE, FULL NAME, CODE
Events2, 1/3/24, Peter Smith, 2

as mentioned above I need to get to

Name, DATE, FIRST NAME, SURNAME, FULL NAME, CODE
Events1, 1/2/24, John, Smith, null, null, 3
Events2, 1/3/24, null, null, Peter Smith, 2

Thanks for any guidance.

Answer 1

Create a custom function by inserting the code below into a blank query using the Advanced Editor.

Name this function fnProcess Tables

(t as table)=>

let 
    Source = Table.PromoteHeaders(Table.RemoveFirstN(t,1), [PromoteAllScalars=true])
in  
    Source

For your main query, paste the code below into another blank query

let
    #"Range List" = {"Events1","Events2"},
    #"Column Order" = {"DATE","FIRST NAME","SURNAME","FULL NAME","CODE"},
    #"Column Types" = { {"DATE", type nullable date},
                        {"FIRST NAME", type nullable text},
                        {"SURNAME", type nullable text},
                        {"FULL NAME",type nullable text},
                        {"CODE", Int64.Type}},

    Source = Excel.CurrentWorkbook(),
    Tables = Table.SelectRows(Source, each List.Contains(#"Range List", [Name]))[Content],
    #"Process Tables" = List.Accumulate(
        {1..List.Count(Tables)-1},
        {#"fnProcess Tables"(Tables{0})},
        (s,c)=>s &  {#"fnProcess Tables"(Tables{c})}),
    #"Append" = Table.Combine(#"Process Tables"),
    #"Reorder" = Table.ReorderColumns(#"Append", #"Column Order"),
    #"Type" = Table.TransformColumnTypes(#"Reorder", #"Column Types")
        
in
    #"Type"

You may need to make some changes to fit your actual data set.

Events1 and Events2 Named Ranges

Results