Power Query is behoorlijk krachtig. Maar ik sta op het punt om Power Query Squared te doen … een query voor één pagina schrijven en vervolgens Excel dezelfde query laten uitvoeren voor een hele lijst met webpagina's.
Bekijk video
- De truc van vandaag is aangepast van het M is for Data Monkey-boek
- Bouw een query om gegevens van één webpagina te krijgen
- Bewerk de query om deze in een functie te veranderen met (VariableName) => voor Let
- Wijzig de hard-coded URL in VariableName
- Hernoem de query naar fxWeather
- Sluiten en laden. De gegevens zullen verdwijnen.
- Gebruik Excel Trickery om een tabel met alle URL's te maken
- Maak een query op basis van die tabel.
- Voeg een nieuwe kolom Weer toe
=fxWeather((URL)) - Vouw de kolom uit. Schakel Prefix uit
- Verbazingwekkend!
Videotranscriptie
Leer Excel van Podcast, aflevering 2056: Power Query Squared
Hé, welkom terug bij de netcast, ik ben Bill Jelen. Ik kwam deze truc tegen toen ik me voorbereidde op een seminar op een conferentie in Dallas genaamd Excelapalooza, de grootste Excel-conferentienaam ooit. Je zou het elk jaar in september in Dallas moeten bekijken.
En dank aan Ken Puls en Miguel Escobar, want ik moest een uur aan powerquery doen, dus ik haalde natuurlijk hun geweldige boek tevoorschijn, 's werelds beste boek over Power Query. Ik bladerde door het boek en ik zag dat ze één techniek hadden en ik zei: “Wacht even. Ik ga kijken of ik deze techniek kan aanpassen. " En dit is voor mij geweldig in machtsvragen. En hier is wat we gaan doen, we gaan één stroomvraag doen en dan gaan we die stroomvraag, die vraag, tientallen keren uitvoeren, oké?
En dus, het voorbeeld dat ik bedacht, was waar ik gegevens van een webpagina wilde halen, oké. En ik ging naar - ik zocht gewoon naar enkele webpagina's die ik kon - die ik als voorbeeld kon gebruiken. Ik kwam terecht bij Weather Underground en hier is de URL, en je kunt zien dat ik in Dallas-Fort Worth was, dus we halen gegevens op voor Dallas en het lijkt op 2 januari 2015. Dus daar in de URL staan de parameters , Rechtsaf? En dat is een URL die gewoon rijp is om dit ding te laten werken.
We zullen de webpagina snel bekijken, hoewel deze niet zo belangrijk is. Zie je, er zijn veel verschillende gegevens hier op de webpagina en ik besloot dat ik gewoon zou proberen om neerslag en hoge en lage temperaturen te krijgen. En hier is die vraag. En laat me je hier vertellen dat deze podcast niet gaat over het maken van deze zoekopdracht, nieuwe zoekopdracht, van andere bronnen, van internet, specificeer de URL en vervolgens een aantal stappen die ik hier niet ga beschrijven om mijn laatste antwoord van Max Temp, Min Temp en neerslag. Het punt is dat je je eigen vraag gaat gebruiken en deze voor een heleboel dingen laat werken.
Dus ik klik op Sluiten en laden en deze query werkt, het retourneert mijn enige rij. Alles is fantastisch. En ik kom terug, ik ga deze query bewerken en ik ga naar View, Advanced Editor. Ik ga deze vraag beantwoorden en ik ga er een functie van maken, oké? Dus hier, voor het woord LET, druk ik op Enter. En tussen haakjes, ik ga het een variabele geven (MyURL) en dan => kleine pijl daar, oké? Stoer. En dan hier beneden, waar ze de URL tussen aanhalingstekens hebben, wil ik de hele URL inclusief de aanhalingstekens verwijderen en dan mijn variabelenaam MyURL typen, oké. Dus wat we zeggen is, we gaan het doorgeven aan URL en het zal dezelfde zoekopdracht uitvoeren, maar met welke URL we het ook doorgeven.
Nu, een paar verontrustende dingen hier, als ik op Gereed klik, oh mannen! Al mijn toegepaste stappen zijn verdwenen en ze willen dat ik een parameter voer. Negeer dat allemaal gewoon. We gaan dit hernoemen; we gaan het fxWeather noemen. FX is natuurlijk de afkorting voor functie en je moet deze naam echt onthouden en onthouden welke letters met een hoofdletter worden geschreven, dat zal binnen een paar minuten erg belangrijk zijn. Home, Close & Load en BAM! Alles is weg. Oh nee! Maar dat is oke. Oké, dus we weten dat het er is. Het is alleen een verbinding. Nu, ik ga hierheen komen en dit is gewoon een regelrechte oude Excel, oké? Dus hier is de URL, ik heb deze uitgesplitst in het eerste deel van de URL, het laatste deel van de URL. Ik weet dat ik de date moet nemen; Ik moet het opmaken in dit rare jaarformaat,maand en dag, dus ik gebruikte de TEXT-functie om dat te doen. Voer hier de startdatum in. Ik kan zelfs van vliegveld wisselen, dus nu ben ik heen en weer. Laten we MCO voor Orlando doen en wat recente gegevens doen. Dus ik begin op 1-10-2016, oké. Dus nu hebben we deze geweldige kleine tafel hier opgesteld. En het moet trouwens wel een tafel zijn. U moet het formaat gebruiken als tabel of Ctrl + T. Dus, weet je, dit is gewoon die datum pakken en dan + 1 + 1 + 1. Ik formatteer het, ik bouw de URL.U moet het formaat gebruiken als tabel of Ctrl + T. Dus, weet je, dit is gewoon die datum pakken en dan + 1 + 1 + 1. Ik formatteer het, ik bouw de URL.U moet het formaat gebruiken als tabel of Ctrl + T. Dus, weet je, dit is gewoon die datum pakken en dan + 1 + 1 + 1. Ik formatteer het, ik bouw de URL.
Oké, we gaan een query maken op basis van deze tabel. Oké, en daar is mijn informatie. Ik ga een nieuwe kolom toevoegen, een aangepaste kolom toevoegen, de kolommen zouden Weer heten en de formule wordt = fxWeather. Zorg ervoor dat het exact dezelfde hoofd- en kleine letters zijn, en we voegen dat veld met de naam URL zo in, haakjes sluiten. Geen syntaxisfouten, klik op OK. Ze willen hier meer weten over privacy, dit zijn allemaal openbare gegevens, klik op Opslaan, oké. Dus er is onze date. Het is grappig dat ze mijn formaat hebben veranderd in iets dat niet lijkt op waar ik mee begon. En dan is hier Weer met het uitvouwsymbool. Dus ik ga op het uitvouwsymbool klikken, schakel het vinkje uit Gebruik originele kolomnaam als voorvoegsel. Ik wil de Max, de Min, de Precipitation,Klik OK. Oké, en nu heb ik alleen de datum en die informatie nodig. Dus ik klik met de rechtermuisknop en verwijder deze kolom, klik met de rechtermuisknop en verwijder deze kolom. Hier heb ik die tijd niet nodig, dus ik zal zeggen dat dit maar een date is, oké. En kijk wat het doet, elke datum dat ik het passeer; het brengt de hoge, de lage en de neerslag voor Orlando terug. Elke rij hier gaat naar een andere webpagina. Stel je voor, als het niet 15 rijen maar 5.000 rijen waren, zou je het instellen om 's nachts te draaien. Ik schreef hiervoor macro's. In feite is een van de webpagina's waar we naartoe gaan, hoe je een macro kunt bouwen om webpagina's van duizend verschillende webpagina's op een site te schrapen, niet meer nodig met powerquery.Ik klik met de rechtermuisknop en verwijder deze kolom, klik met de rechtermuisknop en verwijder deze kolom. Hier heb ik die tijd niet nodig, dus ik zal zeggen dat dit maar een date is, oké. En kijk wat het doet, elke datum dat ik er langs kom; het brengt de hoge, de lage en de neerslag voor Orlando terug. Elke rij hier gaat naar een andere webpagina. Stel je voor, als het niet 15 rijen maar 5.000 rijen waren, zou je het instellen om 's nachts te draaien. Ik schreef hiervoor macro's. In feite is een van de webpagina's waar we naartoe gaan, hoe je een macro kunt bouwen om webpagina's van duizend verschillende webpagina's op een site te schrapen, niet meer nodig met powerquery.Ik klik met de rechtermuisknop en verwijder deze kolom, klik met de rechtermuisknop en verwijder deze kolom. Hier heb ik die tijd niet nodig, dus ik zal zeggen dat dit maar een date is, oké. En kijk wat het doet, elke datum dat ik er langs kom; het brengt de hoge, de lage en de neerslag voor Orlando terug. Elke rij hier gaat naar een andere webpagina. Stel je voor, als het niet 15 rijen maar 5.000 rijen waren, zou je het instellen om 's nachts te draaien. Ik schreef hiervoor macro's. In feite is een van de webpagina's waar we naartoe gaan, hoe je een macro kunt bouwen om webpagina's van duizend verschillende webpagina's op een site te schrapen, niet meer nodig met powerquery.s terug de hoogte, de laag en de neerslag voor Orlando. Elke rij hier gaat naar een andere webpagina. Stel je voor, als het niet 15 rijen maar 5.000 rijen waren, zou je het instellen om 's nachts te draaien. Ik schreef hiervoor macro's. In feite is een van de webpagina's waar we naartoe gaan, hoe je een macro kunt bouwen om webpagina's van duizend verschillende webpagina's op een site te schrapen, niet meer nodig met powerquery.s terug de hoogte, de laag en de neerslag voor Orlando. Elke rij hier gaat naar een andere webpagina. Stel je voor, als het niet 15 rijen maar 5.000 rijen waren, zou je het instellen om 's nachts te draaien. Ik schreef hiervoor macro's. In feite is een van de webpagina's waar we naartoe gaan, hoe je een macro kunt bouwen om webpagina's van duizend verschillende webpagina's op een site te schrapen, niet meer nodig met powerquery.
Now, when I Close & Load, it's funny the preview here is showing me all the results. When I Close & Load, they're actually going to go do each query. And so right now we have preview rows loaded and it will take a good long time for this information to get updated. So, go to lunch, do something especially if you're doing more than 15 rows. And it's funny the preview is correct but they're still going out and could chunk, could chunk, could chunking through each individual row.
And there it's loaded. Is this amazing or what? Hey, I do a lot of Excel seminars, the Power Excel seminar in Orlando, Florida. Look at these beautiful temperatures we have down here on November 4, 2016. My afternoon will be all about Power Query, Power BI, Power Pivot, Power Map. So, I'm going to invite you down to Orlando to check out this seminar. There'll be a link there in the top-right hand corner.
Alright, so recap. Today's trick is from this book, M is for (DATA) MONKEY. We built a query to get one web page and then edit that query to change it into a function. So right before the Let statement variable name => and then change the hard-coded URL to whatever that variable name is. Rename the query to fxWeather, Close & Load, the data disappears. Then, we use some sort of Excel trickery to create a table of all the URLs we want to crawl, create a query from that table. This has to be a Ctrl+T table, add a new column of Weather = fxWeather and again it has to match the case there, (URL), Expand that column, uncheck Prefix. BAM! It is amazing.
Thanks to Ken and Miguel for writing this book. Thanks to you for stopping by. Hope to see you in Orlando on November 4th 2016. See you next time for another netcast from.
Download File
Download het voorbeeldbestand hier: Podcast2056.xlsm
