Apending large excel files in Power BI

[Anonymous]

Hi,

I've 3 excel files in sharepoint relating to 3 years worth of data, each having more than a million rows. To make things more difficult , each has different columns structure, though some are common.  I need to load, transform and append before using them for time intellegence calculations  like ytd, yoy, etc.  I am trying to figure out what is the best solution in terms of ease as well as refresh time. 

The rows in the files are small daily or hourly enteries which can better be summarized but unfortunately its not possible to touch the files at the source.

Thanks for your suggestions.

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can refer the techniques in the following links to make transformation for the data in those 3 excel files first then append them:

Best practices when working with Power Query

Data reduction techniques for Import modeling

Power BI – Quick Tips working with multiple large CSV Files

Best Regards

[Anonymous]

Thanks @Anonymous , very helpful.

[amitchandak]

@Anonymous , the First idea which comes to me, the First aggregate these excel in power Query and clean up and then merge.