Evaluation ran out of memory

[wshenc26]

I am trying to get data from web using power query but it seems that the volume of data is too huge and the following error came out:
Expression.Error: Evaluation ran out of memory and can't continue.

Example of web request:

...Table.FromColumns({Lines.FromBinary(Web.Contents(Origin, [RelativePath=xxxx]))})...

I have tried increasing the cache memory at Options > Data Load > Data Cache Management Options > Maximum allowed to 16000. However, it is still giving the same error if I try to pull large data. 

Details:

I can decide the range of time period of data to pull, such as 1 month, 3 months, or a year. 

I have tried 1 month and it works fine (since the volume of data is smaller than 3 months or a year). 

Is there any setting allows me to pull larger data? Or I have to split them into multiple power query to request data (if this is the way, can anyone enlighten me on how to split and merge them after all the requested data is returned? )

Appreciate if anyone can advise. Thank you!

[ppm1 · Accepted solution]

I would start with a column/table of dates/months over the range of interest (generated in the query), and then add a custom column where you concatenate those values as text into the url. This will result in a column of "Table"s you can then expand to end up with a single table with all your data. This video may be helpful - https://www.youtube.com/watch?v=hHkU8tPhVXs.

Note: if you plan to publish this to the service, you'll want to use the RelativePath option in Web.Contents so you can schedule refresh. If refresh is slow, you could also set up incremental refresh so you don't keep reloading past months (similar to the approach in this video - https://www.youtube.com/watch?v=IVMdg16yBKE).

Pat