Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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
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
Check out the July 2025 Power BI update to learn about new features.