PQ - Clean Data Multiple Workbooks Varying Columns
(question from the web)
Quote:
I am trying to combine data from multiple workbooks with differing number of columns and the data isn't in a table.
| Table1 |
|
|
Table2 |
|
|
|
|
|
Result |
|
| No. |
1 |
|
No. |
1 |
2 |
3 |
4 |
|
No. |
Value |
| Name |
Name1 |
|
Name |
Name1 |
Name2 |
Name3 |
Name4 |
|
Name |
Name1 |
| DoB |
DoB1 |
|
DoB |
DoB1 |
DoB2 |
DoB3 |
DoB4 |
|
DoB |
DoB1 |
| Department |
Dept1 |
|
Department |
Dept1 |
Dept1 |
Dept2 |
Dept3 |
|
Department |
Dept1 |
|
|
|
|
|
|
|
|
|
Name |
Name2 |
|
|
|
|
|
|
|
|
|
DoB |
DoB2 |
|
|
|
|
|
|
|
|
|
Department |
Dept1 |
|
|
|
|
|
|
|
|
|
Name |
Name3 |
|
|
|
|
|
|
|
|
|
DoB |
DoB3 |
|
|
|
|
|
|
|
|
|
Department |
Dept2 |
|
|
|
|
|
|
|
|
|
Name |
Name4 |
|
|
|
|
|
|
|
|
|
DoB |
DoB4 |
|
|
|
|
|
|
|
|
|
Department |
Dept3 |
Code:
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
