Need help with large data sets

%3CLINGO-SUB%20id%3D%22lingo-sub-2046200%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20large%20data%20sets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046200%22%20slang%3D%22en-US%22%3EHi%20all%20wondering%20if%20someone%20can%20help%20me%20with%20a%20solution!%3CBR%20%2F%3E%3CBR%20%2F%3EWe%20have%2010%2B%20suppliers%20that%20send%20us%20multiple%20excel%20spreadsheets%20on%20a%20monthly%20basis%20with%20millions%20of%20lines%20of%20data.%20The%20data%20unfortunately%20doesn't%20come%20formatted%20the%20same%20from%20each%20one%20but%20does%20contain%20the%20same%20information.%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20we%20need%20to%20do%20which%20I%20need%20help%20with%20please!%3A%3CBR%20%2F%3E%3CBR%20%2F%3ECombine%20all%20information%20from%20each%20supplier%20so%20it%20is%20formatted%20the%20same%20(%20really%20stuck%20as%20its%20millions%20of%20lines%20and%20don't%20know%20the%20best%20way%20to%20store%20it%20without%20maxing%20excel%20and%20making%20documents%20slow)%3CBR%20%2F%3E%3CBR%20%2F%3ECreate%20a%20report%20or%20pivot%20on%20all%20the%20data%20on%20a%20monthly%20and%20combined%20yearly%20basis%20(issue%20is%20the%20amount%20of%20data%20being%20millions%20if%20lines%20and%20presenting%20this%20without%20excel%20saying%20you%20have%20gone%20over%20the%20maximum%20amount%20of%20lines.%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20could%20someone%20help%20me%20with%20the%20above%20i%20would%20really%20appreciate%20it.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2046200%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2046288%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20large%20data%20sets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046288%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F923641%22%20target%3D%22_blank%22%3E%40Aread62127%3C%2FA%3E%26nbsp%3BHave%20a%20look%20at%20PowerQuery%20(PQ).%20It%20allows%20you%20to%20connect%20to%20data%20sources%20without%20loading%20everything%20into%20Excel.%20Since%20you%20ask%20the%20question%2C%20I%20assume%20that%20you%20are%20not%20familiar%20with%20it.%20Perhaps%20wise%20to%20have%20a%20look%20at%20the%20information%20in%20the%20attached%20link.%20Then%20you%20will%20get%20an%20idea%20of%20what%20PQ%20can%20do.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Hi all wondering if someone can help me with a solution!

We have 10+ suppliers that send us multiple excel spreadsheets on a monthly basis with millions of lines of data. The data unfortunately doesn't come formatted the same from each one but does contain the same information.

What we need to do which I need help with please!:

Combine all information from each supplier so it is formatted the same ( really stuck as its millions of lines and don't know the best way to store it without maxing excel and making documents slow)

Create a report or pivot on all the data on a monthly and combined yearly basis (issue is the amount of data being millions if lines and presenting this without excel saying you have gone over the maximum amount of lines.

Please could someone help me with the above i would really appreciate it.
3 Replies

@Aread62127 Have a look at PowerQuery (PQ). It allows you to connect to data sources without loading everything into Excel. Since you ask the question, I assume that you are not familiar with it. Perhaps wise to have a look at the information in the attached link. Then you will get an idea of what PQ can do.

https://exceloffthegrid.com/power-query-introduction/ 

Thanks for the reply

Can I ask though, when I create a query and then want to present the results or produce a pivot etc, if the results are 1m+ lines will this cause an issue for excel?

@Aread62127 No problem, you can load the data into the Data Model. More about that in, for example, the link below:

https://chandoo.org/wp/more-than-million-rows-in-excel/