Power Query er ganske kraftig. Men jeg er i ferd med å gjøre Power Query Squared … å skrive et spørsmål for en side og deretter få Excel til å utføre den samme spørringen for en hel liste med websider.
Se på video
- Dagens triks er tilpasset M is for Data Monkey book
- Bygg et spørsmål for å få data fra en webside
- Rediger spørringen for å endre den til en funksjon med (VariableName) => før Let
- Endre den hardkodede URL-adressen til VariableName
- Endre navnet på spørringen til fxWeather
- Lukk og last. Dataene forsvinner.
- Bruk Excel Trickery til å lage en tabell over alle URL-er
- Opprett et spørsmål fra den tabellen.
- Legg til en ny kolonne med vær
=fxWeather((URL)) - Utvid kolonnen. Fjern merket for prefiks
- Fantastisk!
Videoutskrift
Lær Excel fra Podcast, episode 2056: Power Query Squared
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Jeg kom over dette trikset mens jeg forberedte meg på å gjøre et seminar på en konferanse i Dallas som het Excelapalooza, det største Excel-konferansenavnet noensinne. Du bør sjekke det ut hver september i Dallas.
Og æren til Ken Puls og Miguel Escobar fordi jeg måtte gjøre en time på strømforespørsel, så selvfølgelig trakk jeg den fantastiske boken, verdens største bok om Power Query. Jeg bladde gjennom boka, og jeg så at de hadde en teknikk, og jeg sa: “Vent et sekund. Jeg skal se om jeg kan tilpasse denne teknikken. ” Og dette for meg er kjempebra i kraftforespørsel. Og her er hva vi skal gjøre, vi skal ta ett strømforespørsel, og så skal vi kjøre det strømforespørselen, det spørsmålet, dusinvis av ganger, ok?
Og så, eksemplet jeg kom på var hvor jeg ønsket å hente data fra en webside, ok. Og jeg gikk ut til - jeg lette bare etter noen nettsider jeg kunne - som jeg kunne bruke som et eksempel. Jeg havnet på Weather Underground, og her er URL-en, og du kan se at jeg var i Dallas-Fort Worth, så vi henter data for Dallas, og det ser ut som 2. januar 2015. Så akkurat der i URL-en er parametrene , Ikke sant? Og det er en URL som bare er moden for å få denne tingen til å fungere.
Vi tar en rask titt på websiden, selv om den ikke er så viktig. Du ser at det er mange forskjellige data her ute på websiden, og jeg bestemte meg for at jeg bare skulle prøve å få nedbør og høy og lav temperatur. Og her er det spørsmålet. Og la meg fortelle deg akkurat her at denne podcasten ikke handler om hvordan du oppretter denne spørringen, ny spørring, fra andre kilder, fra nettet, spesifiser URL-en og deretter en rekke trinn som jeg ikke kommer til å detaljere her for å få min endelig svar på maks temp, min temp og nedbør. Poenget er at du skal ta ditt eget spørsmål og få det til å fungere for en rekke ting.
Så jeg klikker Lukk og last, og dette spørsmålet fungerer, det returnerer den ene raden min. Alt er kjempebra. Og jeg kommer inn igjen, jeg skal redigere dette spørsmålet og jeg går til View, Advanced Editor. Jeg kommer til å ta dette spørsmålet og skal gjøre det til en funksjon, ok? Så rett her før ordet LET, trykker jeg Enter. Og i parentes skal jeg gi den en variabel (MyURL) og så => liten pil der, ok? Kul. Og så her nede, der de har URL-en i anførselstegn, vil jeg kvitte meg med hele URL-en inkludert sitatene og deretter skrive inn variabelenavnet MyURL, ok. Så det vi sier er at vi sender det til URL, og det kommer til å gjøre det samme spørsmålet, men uansett hvilken URL vi tilfeldigvis sender det.
Nå, et par foruroligende ting her, når jeg klikker Ferdig, å menn! Alle trinnene mine er borte, og de vil at jeg skal angi en parameter. Bare ignorere alt det. Vi skal gi nytt navn til dette; vi skal kalle det fxWeather. FX, selvfølgelig, som forkortelse for funksjon, og du må virkelig huske dette navnet og huske hvilke bokstaver som er stort, det kommer til å være veldig viktig om et par minutter. Hjem, Lukk og last, og BAM! Alt er borte. Å nei! Men det er greit. Greit, så vi vet at det er der. Det er bare en forbindelse. Nå skal jeg komme hit, og dette er bare rett gammel Excel, ok? Så her er URL-en, jeg brøt den ut i den første delen av URL-en, sluttdelen av URL-en. Jeg vet at jeg må ta datoen; Jeg trenger å formatere det i dette rare året,måned, og dag, så jeg brukte TEKST-funksjonen til å gjøre det. Sett inn startdatoen her. Jeg kan til og med bytte flyplass, så nå er jeg frem og tilbake. La oss gjøre MCO for Orlando, og la oss gjøre noen nylige data. Så jeg begynner 1/10/2016, ok. Så nå har vi satt opp dette flotte lille bordet her. Og forresten, det må være et bord. Du må bruke format som tabell eller Ctrl + T. Så du vet, dette er bare å ta tak i den datoen og deretter + 1 + 1 + 1. Jeg formaterer den, jeg bygger URL-en.Du må bruke format som tabell eller Ctrl + T. Så du vet, dette er bare å ta tak i den datoen og deretter + 1 + 1 + 1. Jeg formaterer den, jeg bygger URL-en.Du må bruke format som tabell eller Ctrl + T. Så du vet, dette er bare å ta tak i den datoen og deretter + 1 + 1 + 1. Jeg formaterer den, jeg bygger URL-en.
Greit nå, vi skal lage et spørsmål fra denne tabellen. OK, og det er min informasjon. Jeg skal legge til en ny kolonne, legge til en egendefinert kolonne, kolonnene skulle hetes, og formelen vil være = fxWeather. Forsikre deg om at det er nøyaktig samme bokstav, samme store og små bokstaver, så setter vi inn feltet som heter URL slik, lukker parentes. Ingen syntaksfeil, klikk OK. De vil vite om personvern her, dette er alle offentlige data, klikk Lagre, ok. Så det er vår dato. Det er morsomt at de faktisk har endret formatet mitt til noe som ikke ser ut som det jeg startet med. Og så er det Vær med utvidelsessymbolet. Så jeg kommer til å klikke på utvidelsessymbolet, fjern merket for Bruk original kolonnenavn som prefiks. Jeg vil ha Maks, Min, Nedbør,Klikk OK. Greit, og alt jeg trenger nå er datoen og den informasjonen der ute. Så jeg høyreklikker og fjerner denne kolonnen, høyreklikker og fjerner denne kolonnen. Her borte trenger jeg ikke den tiden, så jeg vil si at dette bare er en dato, ok. Og se hva det gjør hver dato når jeg sender det; det returnerer det høye, det lave og nedbøren for Orlando. Hver rad her går ut til en annen webside. Tenk deg, hvis det ikke var 15 rader, men 5 000 rader, ville du sette det opp til å kjøre over natten. Jeg pleide å skrive makroer for dette. Faktisk er en av nettsidene som er ute på hvordan du bygger en makro for å skrape websider fra tusen forskjellige websider på et nettsted, ikke lenger nødvendig med strømspørsmål.Høyreklikk og fjern denne kolonnen, høyreklikk og fjern denne kolonnen. Her borte trenger jeg ikke den tiden, så jeg vil si at dette bare er en dato, ok. Og se hva det gjør hver dato når jeg sender det; det returnerer det høye, det lave og nedbøren for Orlando. Hver rad her går ut til en annen webside. Tenk deg, hvis det ikke var 15 rader, men 5 000 rader, ville du sette det opp til å kjøre over natten. Jeg pleide å skrive makroer for dette. Faktisk er en av nettsidene hvordan man bygger en makro for å skrape websider fra tusen forskjellige websider på et nettsted, ikke lenger nødvendig med strømspørsmål.Høyreklikk og fjern denne kolonnen, høyreklikk og fjern denne kolonnen. Her borte trenger jeg ikke den tiden, så jeg vil si at dette bare er en dato, ok. Og se hva det gjør hver dato når jeg sender det; det returnerer det høye, det lave og nedbøren for Orlando. Hver rad her går ut til en annen webside. Tenk deg, hvis det ikke var 15 rader, men 5 000 rader, ville du sette det opp til å kjøre over natten. Jeg pleide å skrive makroer for dette. Faktisk er en av nettsidene hvordan man bygger en makro for å skrape websider fra tusen forskjellige websider på et nettsted, ikke lenger nødvendig med strømspørsmål.s returnerer det høye, det lave og nedbøren for Orlando. Hver rad her går ut til en annen webside. Tenk deg, hvis det ikke var 15 rader, men 5 000 rader, ville du sette det opp til å kjøre over natten. Jeg pleide å skrive makroer for dette. Faktisk er en av nettsidene hvordan man bygger en makro for å skrape websider fra tusen forskjellige websider på et nettsted, ikke lenger nødvendig med strømspørsmål.s returnerer det høye, det lave og nedbøren for Orlando. Hver rad her går ut til en annen webside. Tenk deg, hvis det ikke var 15 rader, men 5 000 rader, ville du sette det opp til å kjøre over natten. Jeg pleide å skrive makroer for dette. Faktisk er en av nettsidene som er ute på hvordan du bygger en makro for å skrape websider fra tusen forskjellige websider på et nettsted, ikke lenger nødvendig med strømspørsmål.
Nå, når jeg lukker og laster, er det morsomt at forhåndsvisningen her viser meg alle resultatene. Når jeg lukker og laster, kommer de faktisk til å gjøre hvert spørsmål. Og så akkurat nå har vi forhåndsvisningsrader lastet inn, og det vil ta lang tid før denne informasjonen blir oppdatert. Så gå til lunsj, gjør noe spesielt hvis du gjør mer enn 15 rader. Og det er morsomt at forhåndsvisningen er riktig, men de går fremdeles ut og kan klumpe, klumpe, klumpe gjennom hver enkelt rad.
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
Last ned eksempelfilen her: Podcast2056.xlsm
