Hello,
I have a Power Query which gets the data from three workbooks in one source folder. The workbooks are updated and replaced daily.
Each workbook has in excess of 250 worksheets - sheets are modified daily but adding sheets is unusual.
The Power Query extracts the data from each sheet in each workbook (all columns and all rows and presents it in one worksheet - which for now I import into Access and do what I need).
Questions:
1. If I create the query and run it without removing columns or other edit it works fine today. When I open it tomorrow I will get the message that "We found a problem with some contact in FileName.xlsx. Do you want us to recover as mush as we can? If you trust the source of this workbook. Click yes." The only change is that the workbooks have been updated. Whatever i do other than rewriting and starting from scratch it never runs. I'm sure this isn't right. What can I do ensure it runs each time.
2. I would like to strip the data somewhat by removing columns I don't need and filtering out unnecessary rows or changing column names, but after make an edit to the query and run it I get the error about not enough memory to complete the operation. (task Manager shows i'm barely running 25% of RAM). I remove the filters and add back the columns and it runs through fine. All tables together is <100,000 rows and 30 columns wide. (as above when i say runs that is just today, tomorrow i will be back to problem 1.
Any thoughts please.
thanks
I have a Power Query which gets the data from three workbooks in one source folder. The workbooks are updated and replaced daily.
Each workbook has in excess of 250 worksheets - sheets are modified daily but adding sheets is unusual.
The Power Query extracts the data from each sheet in each workbook (all columns and all rows and presents it in one worksheet - which for now I import into Access and do what I need).
Questions:
1. If I create the query and run it without removing columns or other edit it works fine today. When I open it tomorrow I will get the message that "We found a problem with some contact in FileName.xlsx. Do you want us to recover as mush as we can? If you trust the source of this workbook. Click yes." The only change is that the workbooks have been updated. Whatever i do other than rewriting and starting from scratch it never runs. I'm sure this isn't right. What can I do ensure it runs each time.
2. I would like to strip the data somewhat by removing columns I don't need and filtering out unnecessary rows or changing column names, but after make an edit to the query and run it I get the error about not enough memory to complete the operation. (task Manager shows i'm barely running 25% of RAM). I remove the filters and add back the columns and it runs through fine. All tables together is <100,000 rows and 30 columns wide. (as above when i say runs that is just today, tomorrow i will be back to problem 1.
Any thoughts please.
thanks