Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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?

 

lbendlin_0-1631666612406.png

 

View solution in original post

4 REPLIES 4
v-yingjl
Community Support
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
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, 

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 !

lbendlin
Super User
Super User

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?

 

lbendlin_0-1631666612406.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors