Multiple Excel sources and many products

[BigByteBooks]

Hi experts,

I have four years of sales data on over 600 products. All of that data is in 45 Excel spreadsheets by month. There are all kinds of things I'd like to do with this but the most important right now is to graph the effect of price point against royalty.

In other words, for any single product ID, I want to be able to see IF there is an effect of price point on royalty and hopefully find the sweet spot. So I need to see across time the changes in price point and the effect, if any, on royalty totals.

Because this data is spread among so many sources, should I first combine the spreadsheets rather than importing each one and then creating a dashboard report that I can filter on various fields?

Thanks in advance for any input.

[CheenuSing · Accepted solution]

@BigByteBooks

It is better to combine the 45 excel sheets into a single one and then import into Power BI

Which version of Excel are you using.  Make sure the version you are using is able to accommodate all the data into a single sheet in the combined excel file.  Otherwise you will have formulate you own strategy of combining few excel files into one and hence have a limited number of excel files to be imported. 

If you are loading mutiple excel files of some data structure then you should append the data from each file to the data model and not create as different tables in the model.  Check out on appending data to existing data model.

Cheers

CheenuSing

[BigByteBooks]

 Thanks, CheenuSing.