Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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