Load, normalise and combine tables

I'm trying to attempt something which is quite beyond my Power Query M skills so any help would be appreciated.

I have a bunch of Excel files in different places on SharePoint with direct links to those files in a table such as:

Each file is slightly different, has different headings, different column names, different number of columns.

I would like to combine the files in a single, normalised table.  The steps would be to load each table, add index, normalise the data, combine the resulting tables into one master table.

So for example, inputs:

Load and add index:

Normalise and merge:

Of course I would like for the process to by dynamic, so no hard coding anything.
Any help and/or pointers would be very much appreciated.

Edit.  It turned out to be much easier than I thought it would be.  I've created a custom function as below and it seems to be working:

(sheetName) =>
let
Source = Excel.Workbook(Web.Contents(sheetName), null, true),

Expand = Table.ExpandTableColumn(
Source,
"Data",
List.Union(List.Transform(Source[Data], each Table.ColumnNames(_)))),
#"Removed Columns" = Table.RemoveColumns(Expand,{"Name", "Item", "Kind", "Hidden"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),

#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value")

in
#"Unpivoted Other Columns"