Adding column from another Excel source

[KelvinMorel]

Hi all,

Salesmen of the company need to fill in a Excel list for each event they make, so we have multiple Excel files. Here the "problem" I'm having, I would like to "merge" all those files in one but in this case I would like to only add the column with results.

Files are something like following:

File 1

Questions	Answers
Q A	1
Q B	2

File 2

Questions	Answers
Q A	3
Q B	4

Merged File should be something like following

Questions	Answers	Answers
Q A	1	3
Q B	2	4

Is something like this possible?

Cheers

[dufoq3 · Accepted solution]

Hi @KelvinMorel, if you want to make it dynamic - you can do it this way:

Lets assume you have such files in same folder, they looks like this and you have questions and answers stored in Sheet1 in each file:

File1

File2

Result

Change folder addres in Source step:

let
    Source = Folder.Files("c:\Downloads\PowerQueryForum\KelvinMorel\"),
    FilteredExcelFiles = Table.SelectRows(Source, each Text.StartsWith([Extension], ".xls")),
    BinaryToTable = Table.TransformColumns(FilteredExcelFiles, {{"Content", each Excel.Workbook(_, true){[Name = "Sheet1"]}[Data], type table}}),
    CombinedTables = Table.Combine(BinaryToTable[Content]),
    GroupedRows = Table.Group(CombinedTables, {"Questions"}, {{"All", each 
        [ a = Table.RemoveColumns(Table.FirstN(_, 1), {"Answers"}),
          b = List.Accumulate({0..List.Count([Answers]) -1}, a, (s,c)=> Table.AddColumn(s, "Answer" & Text.From(c+1), (x)=> [Answers]{c}, type text))
        ][b], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

[Anonymous]

in excel we can do this with the help of vlookup from the another workbook  and in power query we can merge both files and expand only answers columns