Combine Excel files columnwise by Power Query

0
votes

I need to create an overview table for the results of a survey. Data (answers) is stored in multiple Excel files and I'm wondering if there is a way to achieve it by using Power Query.

All the files (questionnaires) are saved in the same folder and have exactly the same structure. They consist of two columns, while the first column lists the questions (Q) and the second column - the answers (A), e.g.:

Table 1:

Questions Answers
Q1 A11
Q2 A12
Q3 A13

Table 2:

Questions Answers
Q1 A21
Q2 A22
Q3 A23

The resulting overview table should look like:

Questions Table 1 Table 2
Q1 A11 A21
Q2 A12 A22
Q3 A13 A23

My Power Query skills are rather limited, I manage to append single files/tables below each other, but what I rather need is a kind of columnswise combination of tables.

Your help is much appreciated! Max

1
excelpowerquery
Is this just a single merge or are you trying to combine a lot more than two tables? – Alexis Olson
If they have exactly the same structure, how do you know which is Table1 and which Table2? – Ron Rosenfeld
Thank you for your questions! The file names are different, e.g. "AnswersMax.xlsx" and "MySurveyAnswers.xlsx" and each file contains exactly one table. The structure of each table (amount of rows, columns, format) is, indeed, exactly the same, but the answers might be different. What I'm trying is to collect all the answers from different files in one single overview table. Thank you! Max – Maksim

1 Answers

0
votes

Proceed like this..

Step-1 Data (menu) > Get & Transform > New Query > From file > from Folder

Step-2 Combine & Transform

Step-3 pivot on source column (Advanced options - second column and don't aggregate)

You'll get desired view.

See the following screenshots for help

enter image description here