Get & Transform Data (Power Query) Question

[daysleeper15]

I have Excel 2016 on my computer and am trying to use Power Query for the first time. I am trying to do something that I thought would be easy but I'm having the hardest time figuring out how to make Power Query work. Here is what I am trying to do any tips are appreciated:

I have two excel files that have three columns each. The headers are the same on each, company name, account code, and account balance. For simplicity I have set up each file so that they both have the same number of rows and just entered zero balances into accounts that have no balance (eventually I would like to not have to do this so that I can just pull raw data from ERP and not have to manipulate the raw data).

Each month the balances are updated and work is done to consolidate the two trial balances. Currently I do this by creating a third workbook with links to the two other workbooks. It's not ideal but works for what I am doing today, but looking ahead I am going to have 35 files that will need to be consolidated.

What I would like to be able to do is create a third workbook that pulls the information I need from the other workbooks into one sheet with three column that looks like this:

Column 1 (Header - Account Code), Column 2 (Header - Company 1 Name), Column 3 (Company 2 Name), then the rows will be populated with the account codes in column 1, and the account balances for each company 1 and company 2 in columns 2 and 3.

Thank you
Jim

 

[Andrew_UK]

You need this my friend Save all your monthly/weekly/daily (whatever) documents in the same folder, then follow this guide.

https://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

I've got it working for basically the same concept; we have a pipeline document which our sales team use, but they want to take a snapshot and add their own comments, then management were collating it all - I hate it when you centralise all the reports and create a system that works and all it does in practice is that people copy/paste it into a new sheet, send via email attachments, collate it all and then complain that the system is so manual - anyone can relate?

So, I made it so that the export button saves it to a specified location with a controlled name for the file. Then the above Power Query script picks up everything that was in that folder, arranges it all neatly using the creation date of the file and can display all the pivots they could want. Manual processes eliminated :D