cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Power Query very slow in extracting data from website

So I am trying to extract data from a retail website , to get urls first for the products , then from the URL extracting the data.

 

So I am tryingn to get 29 pages of data , each page 96 products , 2784 products data .

 

Problem is everthing works fine except data loading into the model from power query , its very slow , it take 5-6 hours to get the data and ultimately it fails , though i can see the data in power query editor screen.

 

How can I increase the Power Query Speed to extract data from internet , and also how to load it faster ,without it again extracting data while loading in to model. And also not crashing finally.

 

below is the code for same.

 

et Source = {1..29}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Page"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each women([Page])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Link", "Merged", "desc", "Type", "URL"}, {"Link", "Merged", "desc", "Type", "URL"}), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded Custom"), #"Removed Columns" = Table.RemoveColumns(#"Removed Errors",{"Page", "Link", "Merged", "desc", "Type"}), #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "skudata", each skudata([URL])), #"Expanded skudata" = Table.ExpandTableColumn(#"Invoked Custom Function", "skudata", {"SKU", "cl", "cl 1.", "Suppliet", "Supplier No.", "Column1", "Column2", "Column3", "Supplier Style No."}, {"SKU", "cl", "cl 1.", "Suppliet", "Supplier No.", "Column1", "Column2", "Column3", "Supplier Style No."}) in #"Expanded skudata"

4 REPLIES 4
Highlighted
Community Support
Community Support

@inder,

 

Try to disable File > Options and settings > Options > CURRENT FILE > Data Load > Allow data preview to download in the background.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

I have tried the preview diasble  also , still its very slow and unable to extract all data for 29 pages .

 

The URL link extraction function works fine even for 400 pages , the first function (women)  created to extract links from the website..

 

 

 

Highlighted

Guys this is really annoying, extremely slow, I have tried against several webpages and to no avail. Currently it seems it doesn't run in parallel. Let's say you have 1000 pages then it should at least assign the workload simultaneously, with R or Python you can run a loop in parallel, without that feature, is a deal breaker.
 
I was hoping to use a M query in a package in SSIS, but the performance is simply not enterprise or business ready!
Highlighted

Did you find any solution? I have same problem. I'm also trying to get some data from website and its terribly slow. It's taking 6-7 hours.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors