Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power Query running slow through google search

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

  • 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?

     

     

4 Replies

  • 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?

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    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 !

  • v-yingjl's avatar
    v-yingjl
    Community Support

    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's avatar
      Anonymous
      Not applicable

      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,