PQ - Clean Data Multiple Workbooks Varying Columns

[sandy666]

(question from the web (https://www.mrexcel.com/board/threads/clean-data-multiple-workbooks-varying-columns.1162468/))

I am trying to combine data from multiple workbooks with differing number of columns and the data isn't in a table.

Table1Table2Result

No.1No.1234No.Value

NameName1NameName1Name2Name3Name4NameName1

DoBDoB1DoBDoB1DoB2DoB3DoB4DoBDoB1

DepartmentDept1DepartmentDept1Dept1Dept2Dept3Depar tmentDept1

NameName2

DoBDoB2

DepartmentDept1

NameName3

DoBDoB3

DepartmentDept2

NameName4

DoBDoB4

DepartmentDept3


let
Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Append = Table.Combine({Source1, Source2}),
Filter = Table.SelectRows(Append, each ([4] <> null)),
Index = Table.AddIndexColumn(Filter, "Index", 1, 1),
Unpivot = Table.UnpivotOtherColumns(Index, {"Index", "No."}, "Attribute", "Value"),
Sort = Table.Sort(Unpivot,{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}),
TSC = Table.SelectColumns(Sort,{"No.", "Value"})
in
TSC
Description is poor and examples are not representative, It would be better if there were more attachments with data