December 18, 2021
Hello, this is my first post to this forum. I've been using PQ for a little over a week and am experimenting with pulling info off the web for use in queries. I set up a file to harvest player names, positions and other data from the Major League Baseball (MLB) site. This site has the entire 40-man rosters for all 30 teams, each roster hosted on its own individual site (e.g., the Toronto Bluejays' roster is at https://www.mlb.com/bluejays/r.....ter/40-man.
There are (4) queries to pull data from these URLs, named Pitchers, Catchers, Infielders and Outfielders. Each correspond to the named table on the URL. Once I have set all the data to identical query forms I append everything in 1 final table, sort by player name, etc. and export to a table. The cols would be Role (pitcher, catcher, infielder or outfielder), Last name, First name, Jersey Number amongst others.
I figured out how to set up a user drop-down list (taken from a table hidden in a sheet in the file), and this would index/match the corresponding URL for that team (taken from the same hidden sheet). This is for one team only and I take that URL and create a parameter for that which I used in my first 4 queries (instead of the manual "http://... " entry I did to set things up for manual updates.
This is working quite well, and I'm pleased how PQ pulls this data off the web. I'm still amazed at the capabilities of PQ. Here's my question/challenge:
I'd like to be able to repeat this for all 30 teams, and have the team name inserted as the first exported table column so I can view the rosters for all 30 teams in 1 table. I am stumped trying to figure out how to automate and use the table with team names/URLs so that my 4 harvesting columns pull out that data without manual intervention/typing, and the 5th appending column appends the 30 team rosters into one file. I could brute force it and repeat the 4 harvesting queries for each team, but come ON: that's 120 separate queries to add (and maybe 150 if I have to add 30 more appending queries), and as much as I've sort of fallen head over heels with Power Query, I'm just not THAT much in love : /
I'd appreciate any guidance on this, as although this is kind of a fun project, once I have the learnings there I will apply them to some serious work projects using Sharepoint, and this has some great applications on that front. I recognize this description has a LOT of typing and I've uploaded the file if somebody wants to give it a go and get back to me. I'd love to hear how an experienced user cracks this; doubtless there's an easy answer but I'm so new at PQ I can't find it regardless of numerous google and youtube searches.
Many thanks in advance, and looking forward to hearing any feedback. All comments/suggestions are welcome.
December 7, 2016
I think you will find an answer to your question in this article.
The data in the MLB site seems to be structured the same way, so what is described in the article should be doable for this scenario.