Trying to combine multiple pricing guides into a power pivot

[Dswenni]

I recently became GM of a very busy Bar with a small restaurant in it. It is two business' under one roof, separated only by the varying atmospheres. I need to combine the multiple pricing guides from 4 different liquor distributors. They all sent me versions formatted differently. They all have similar info, but would like them combined and have the same headers

[#2]

@Dswenni 

I wonder why you're suggesting Power Pivot rather than Power Query....but there are a few other questions that I and others probably would want to ask you first.

1. How will you be using the combined data? To determine the best price for a given liquor? Or combo order of multiple items?
2. Are you confident that the names/descriptions are consistent (I mean exactly consistent) for any one liquor, from vendor to vendor? If not, queries, lookups, and pivot table summaries can get bogged down.
3. How often are these price lists updated? If infrequently (say, quarterly or less frequently) then a copy/paste manual process to create a single database might suffice. If monthly or more frequently a more robust process is called for.

I face something similar in consolidating statements from four credit cards and a bank so as to develop a pivot table to track expenses by category. My solution (so far) is copying and pasting into a single, with attendant modification of date formats and column headings and column sequences. Less than elegant, but the resulting pivot table is very nice.  :)

[#3]

Perhaps you are corrct on pivot query. My strengths are not spreadsheets or Excel period. I just want to combine them all with 5 headers for all. The descriptions are good, and yes prices can change month to month. I need something so when i go in and redo our POS system, i know which tier our top 50 liqours should be in@mathetes 

[#4]

@Dswenni 

I agree with @mathetes , Power Query is more suitable and the logic of combining shall be defined first. Majority of us is not familiar with this business logic, even if the take a drink from time to time. There are Unit price, Case prise, CS price, qty deal, price by date, etc. - how to merge all that.

As for the technique, perhaps most logical is to place all these files in separate folder and Power Query them from master file. As soon as new file appears put it in that folder replacing previous one and Refresh All.