Advice needed: Power Query vs. Access

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
181
Hello, and thank you in advance for providing input or advice if you can.

I have a set of data that is almost 800,000 rows with multiple columns. I initially have to split the larger file up into smaller files to get it into ArcMap so I can spatially join the records and export it back to excel and then rejoin the data by bringing the newly acquired variables into the original set using VLOOKUP.

I then have to run this data through a series of procedures combining some of the variables and creating new variables according to certain parameters so I can flag and subsequently filter the record numbers to only what we consider valid by our criteria. I have done this in excel 2013 with a series of formulas, VLOOKUPS, and pivots. I am pretty happy with the process, BUT, the number of records I will have in the next download will exceed what excel can handle, and that data will subsequently get added to the initial data set as well. I would prefer to not have to open the data in Access to split it up into smaller fragments and attempt to run it through all of my procedures in excel, some of which need the whole data set to work. I am using Excel 2013, and unfortunately the tutorial I was trying to use at Lynda.com was for Power Query 2016, and I cannot find the same features displayed in the 2013 version to follow along, nor do I know if that program will actually work for my circumstances.

I haven't really ever worked in Access before, nor used Power Query, but I am looking for some advice as to which program may work the best for what I need to do with an already large and continually growing data set. It would be nice if I could use the capabilities of one of these programs to run the processes I have already figured out, but I would like to put my time into learning the one that best will fit my needs. This is scientific data, not business data, so I am not running accounting functions, but using a combination of text and numeric fields (dates etc.), along with geographic localities, to determine validity of the records. I am in this for the long haul, and I know I need to branch out and learn a new program that can handle the amount of data I will be having to process, the question is, which one?
Any thoughts and advice would be appreciated.
Thank you!
Maggie :eek:
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top