getting an error when doing large batch size extractions

[trackster]

I tried to do a 1,000,000 record pull which took quite a while. when the process finished I got this error: expression.error: evaluation resulted in a stack overflow and cannot continue.

However, when I do small batches the data comes over fine. Is there a way to get around this error?

Much appreciated

 

[Matt Allington]

How about you provide some more Informaton, such as
what is the source?
how many columns?
what is the destination and version?
are you using Power Query?
is there any transformation

 

[trackster]

here is the function that I am using:
(page as number) as table =>
let
Source = Web.Page(Web.Contents("http://www.wvculture.org/vrr/va_dcdetail.aspx?Id=" & Number.ToText(page))),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column3"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Death Record Detail...", type text}, {"Name:", type text}, {"Sex:", type text}, {"Death Date:", type text}, {"Death Place:", type text}, {"Age at Death:", type text}, {"Burial Place:", type text}, {"Burial Date:", type text}, {"Cemetery:", type text}, {"Funeral Home:", type text}, {"Birth Date:", type text}, {"Birth Place:", type text}, {"Marital Status:", type text}, {"Spouse:", type text}, {"Occupation:", type text}, {"Address:", type text}, {"Residence:", type text}, {"Mother:", type text}, {"Mother's Birth Place:", type text}, {"Father:", type text}, {"Father's Birth Place:", type text}, {"Informant:", type text}})
in
#"Changed Type1"

here is the invoked function:
let
Source = List.Accumulate(
{98000..108000},
#table ({},{}),
(state, current) => Table.Combine({state,fxWVdeathcertificates(current)})
)
in
Source

I am using 23 columns
not sure how to answer the question about the destination and version.
I am using power query.
I am transposing the data.

 

[Matt Allington]

So It is coming from a web page I guess. This will be limited by the speed the web server responds, and it seems you are making 10,000 requests to the server. I also see a transpose step in there, that could add some overhead, but may not be discretionary.

I doubt you can speed this up much

 

[trackster]

is there a way to do batches? for instance, 10,000 records at a time added to the same sheet? it would be a long process, but it could work that way.