MFBirdy
New member
- Joined
- Feb 6, 2019
- Messages
- 1
- Reaction score
- 0
- Points
- 0
- Location
- Maine
- Excel Version(s)
- Office Pro Plus 2019
Hello and thank you in advance if you can help,
This is my first time on this forum, and I am glad to have found it. First, I am using Excel 2019 (64Bit) with 16 Gigs of RAM, and an AMD Phenom(tm) II X6 1100T Processor with 6 cores. I am new to PQ and VERY excited at what I am finding, but I have run into a major road block.
I brought two tables over to Power Query, one is the raw data with 1,022,694 records and another is a locations table with the same number of records as a lookup. I have three excel tables I brought in as lookups, one with 366 rows, one with 344 rows, and another with just over 4000 rows. All are loaded as connections, added to the data model, and relationships established accordingly. I originally brought the two large files over as text files. Everything was going good, and I was happily working away until after several merge queries and subsequent manipulations (a few days work) , things started to run very slowly, to the point that it might take over an hour to open a query to work on it. I started over, making sure I did as much of the data merge or other manipulations as close to my source query as possible, but it happened all over again. That being said, the base queries opened relatively quickly. I posted on another forum and it was suggested that I should put my data from the text file into a database before bringing it into PQ. So, I put the data into Access, and, low and behold, was working away and am now still dealing with the same issues. The more I do the slower it gets. I replied to the previous post on the other forum, and they suggested SQL instead of Access because of query folding. I tried to download that and get my data in, but it just wasn't working, so I am back at my Access based PQ Workbook limping along.
So, first, should PQ be able to function with two queries based on text files just over a million records, or is it better for me to have put it in Access to cut the # of columns and changed header names to not have spaces etc. before bringing it into PQ. Second, this is a long term project that I am trying to build for and expanding data set that will trickle in over the next five years, so if I need to redo the whole process, I can, I just want to make it as efficient as possible. The data set will continue to grow, and I would expect that it could eventually get to somewhere around 8 million records or so. In other words, if I need to learn SQL to better handle my data prior to bringing it into PQ, I will invest the time to learn to do so, but if PQ should be able to handle the flat files, or Access, for these I can start to determine if it is something I am doing wrong in PQ or if I need a new computer.
I hope this makes sense, and, again, thank you for helping if you can.
Sincerely,
MFBirdy
This is my first time on this forum, and I am glad to have found it. First, I am using Excel 2019 (64Bit) with 16 Gigs of RAM, and an AMD Phenom(tm) II X6 1100T Processor with 6 cores. I am new to PQ and VERY excited at what I am finding, but I have run into a major road block.
I brought two tables over to Power Query, one is the raw data with 1,022,694 records and another is a locations table with the same number of records as a lookup. I have three excel tables I brought in as lookups, one with 366 rows, one with 344 rows, and another with just over 4000 rows. All are loaded as connections, added to the data model, and relationships established accordingly. I originally brought the two large files over as text files. Everything was going good, and I was happily working away until after several merge queries and subsequent manipulations (a few days work) , things started to run very slowly, to the point that it might take over an hour to open a query to work on it. I started over, making sure I did as much of the data merge or other manipulations as close to my source query as possible, but it happened all over again. That being said, the base queries opened relatively quickly. I posted on another forum and it was suggested that I should put my data from the text file into a database before bringing it into PQ. So, I put the data into Access, and, low and behold, was working away and am now still dealing with the same issues. The more I do the slower it gets. I replied to the previous post on the other forum, and they suggested SQL instead of Access because of query folding. I tried to download that and get my data in, but it just wasn't working, so I am back at my Access based PQ Workbook limping along.
So, first, should PQ be able to function with two queries based on text files just over a million records, or is it better for me to have put it in Access to cut the # of columns and changed header names to not have spaces etc. before bringing it into PQ. Second, this is a long term project that I am trying to build for and expanding data set that will trickle in over the next five years, so if I need to redo the whole process, I can, I just want to make it as efficient as possible. The data set will continue to grow, and I would expect that it could eventually get to somewhere around 8 million records or so. In other words, if I need to learn SQL to better handle my data prior to bringing it into PQ, I will invest the time to learn to do so, but if PQ should be able to handle the flat files, or Access, for these I can start to determine if it is something I am doing wrong in PQ or if I need a new computer.
I hope this makes sense, and, again, thank you for helping if you can.
Sincerely,
MFBirdy