Power Query running slow through google search

[Anonymous]

Hi there, 

I am currently working on creating a new data basis and for that I need some informations for companies (APE code in French). I only can find these informations easily on Google when I'm searching "Company name Code APE". 

It worked for 1 or 2 companies but I need to do it for 5500 companies and it is very slow (it takes a day to have 200 companies and after that Excel is not working anymore...). 

Here is my file : https://www.dropbox.com/s/9ckwasmxcubjwid/BBD%20Ent%20%C3%A0%20classer%20-%20Forum.xlsx?dl=0

Here is my script : 

(URL) as table =>

let
    Source = Web.Page(Web.Contents(URL)),
    Data0 = Source{0}[Data],
    #"En-têtes rétrogradés" = Table.DemoteHeaders(Data0),
    #"Type modifié1" = Table.TransformColumnTypes(#"En-têtes rétrogradés",{{"Column1", type text}, {"Column2", type text}}),
    #"Colonnes renommées" = Table.RenameColumns(#"Type modifié1",{{"Column1", "Intitulé"}, {"Column2", "Informations"}}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes renommées",{{"Raison sociale", type text}, {"SOCIETE AIR FRANCE", type text}})
in
    #"Type modifié"

And I followed the steps from this video : https://www.youtube.com/watch?v=STjBoS1rQuQ

For me the problem is that I'm doin a research with Power Query on Google and it is very long... Any suggestion ? Thanks !

PS : I already did all the steps to speed up PQ

[v-yingjl]

Hi @Anonymous ,

You can try to split the entire data table into multiple small data tables and then call the custom function. Each URL will send a query. Sending queries with too many URLs in a table at the same time may cause slowness or long-term failure to respond.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl ! 

Thanks for you message. Yes I did try it before and did not work on Excel (more that 50 URL's were too much). But the solution from @lbendlin on Power BI worked for me (I can do like 1000 links in 15 minutes when I was doing 200 links in a day with Excel...). 

Thank you a lot :!

Best, 

[Anonymous]

Hi ! 

No, it does not have to be in Excel, I'll try with Power BI 🙂 

After that, I would like to extract the actual APE/NAF code and to link this to a company in another shhet. So it can appear in any of the results.

Thanks !

[lbendlin · Accepted solution]

Does it have to be in Excel?  Power BI has a new web table inference that works reasonably well with google search results.

How are you planning to extract the actual APE/NAZ code?  It can appear in any of the results?