Combining 02 files from different workbooks

[Bondtk]

File 1

Month Volume SLA Target
Jan2020 100 90.86% 90%
Feb2020 200 94.23% 90%
Mar2020 300 88.56% 90%


File 2

Month Volume SLA Target
Jan2020 200 97.23% 90%
Feb2020 300 90.23% 90%
Mar2020 450 89.56% 90%


Both files are in different workbooks , have similar data and chart sla % and volume

how do I combine this into one dataset , so the volume to be added together for months
and sla % age avg for each month and I can draw just one chart.

I am using excel 2016 and please advise if its possible in power query to combine it together

so we have the same Jan to March data with volume combined and sla %age as well.

thanks

[Ron Coderre]

Ken Puls explains one way to combine multiple Excel files in this blog post:
https://www.excelguru.ca/blog/2015/0...n-power-query/

Is that something you can work with?

[bluesky63]

if there are not many worksheets, then use from table to import into PQ, each query use split by columns and then use Append as new, close and load back to Excel

There are other ways to combine and most of them without any coding just using UI features in PQ, and just build once and in future just right click and refresh

[Bondtk]

Hi Christopher

I don't want to append these 02 files to get 06 rows....

what I want is to combine the data and have same three rows …

like

File 1

Month Volume SLA Target
Jan2020 100 90.86% 90%
Feb2020 200 94.23% 90%
Mar2020 300 88.56% 90%


File 2

Month Volume SLA Target
Jan2020 100+ 200 97.23% + 90.86%)avg 90%
Feb2020 200 +300 90.23% +94.23%)avg 90%
Mar2020 300 + 450 89.56% +88.56%)avg 90%


so the idea is to combine this data ,

is it possible with power query , that's the question.

[JeteMc]

Not to beat a dead horse, but if you used Christopher's method you could then use a pivot table to display the values as described in post #4.
Let us know if you have any questions.

[Bondtk]

thanks for your help.

[bluesky63]

if there are not many worksheets, then use from table to import into PQ, each query use split by columns and then use Append as new, close and load back to Excel

There are other ways to combine and most of them without any coding just using UI features in PQ, and just build once and in future just right click and refresh

Update using Power Query

[JeteMc]

@bluesky63, Very nicely done!
@BondTK, In the event that the original tables are in multiple columns the attached may help:

Please Login or Register  to view this content.

Let us know if you have any questions.