CODEWITHSUNDEEP
excelpowerqueryxls
user13088395
user13088395

Reputation:

How to mass transpose and merge multiple xls files?

I have more than 30 seperate .xls files similar to this:

.xls 1:

a   11  22  33  54
b   2   44   4  5
c   3   4    5  3

.xls 2

a   12  22  35  58
b   2   44   4  5
c   3   4    5  3

I want to transpose and merge them (possibly through Power Query?), so they look like this:

a     b    c
11    2    3
22    44   4
33    4    5
54    5    3
12    2    3
22    44   4
35    4    5
58    5    3

How do I make this? Thank you very much.

Upvotes: 0

Views: 1239

Answers (2)

horseyride
horseyride

Reputation: 21413

Try below; assumes data is on Sheet1 Then filter out additional headers

let Source = Folder.Files("C:\directory\subdirectory"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xls")),
#"Pull Data" = Table.AddColumn(#"Filtered Rows", "Data", each Excel.Workbook([Content], null, true){[Item="Sheet1",Kind="Sheet"]}[Data]),
Invert = Table.TransformColumns(#"Pull Data", {{"Data", each Table.Transpose(_)}}),
#"Removed Columns" = Table.RemoveColumns(Invert,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
MaxColumns = List.Max(List.Transform(#"Removed Columns"[Data], each Table.ColumnCount(_))),
#"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Data", List.Transform({1..MaxColumns}, each "Column" & Number.ToText(_))),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Content", [PromoteAllScalars=true])
in #"Promoted Headers"

Upvotes: 0

Marc Pincince
Marc Pincince

Reputation: 5202

Try this.

  1. Put your excel files in the same folder.
  2. Click Get Data, then click From File, then click From Folder.
  3. Either type the path of your folder with the excel files in it or click the Browse button and navigate to your folder with the excel files in it and select that folder and click OK, then click OK.
  4. Click the Transform Data button.
  5. Click the drop-down arrow button in the Extension column, then click on Text Filters and Equal... and type .xls (with the dot included) in the text box to the right of the first equals, and click OK.
  6. Click the double down arrows button in the Content column.
  7. Select the common sheet that you want to use and click OK.
  8. Click on the query that is named Transform Sample File.
  9. Click Transform, then click Transpose.
  10. Click Transform, then click Use First Row as Headers.
  11. Click on the query that is named the same as your excel folder and listed under Other Queries.
  12. Delete the final (most likely the only) Changed Type applied step in the right side pane.

Upvotes: 1

Related Questions