Power Query er ret stærk. Men jeg er ved at gøre Power Query Squared … at skrive en forespørgsel til en side og derefter have Excel til at udføre den samme forespørgsel for en hel liste med websider.
Se video
- Dagens trick er tilpasset fra M is for Data Monkey book
- Byg en forespørgsel for at hente data fra en webside
- Rediger forespørgslen for at ændre den til en funktion med (VariableName) => før Let
- Skift den hårdkodede URL til VariableName
- Omdøb forespørgslen til fxWeather
- Luk og indlæs. Dataene forsvinder.
- Brug Excel Trickery til at oprette en tabel med alle URL'er
- Opret en forespørgsel fra den pågældende tabel.
- Tilføj en ny kolonne med vejr
=fxWeather((URL)) - Udvid kolonnen. Fjern markeringen af præfikset
- Fantastiske!
Videoudskrift
Lær Excel fra Podcast, afsnit 2056: Power Query Squared
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Jeg stødte på dette trick, mens jeg forberedte mig på at lave et seminar på en konference i Dallas, der hedder Excelapalooza, det største Excel-konferencenavn nogensinde. Du bør tjekke det ud hver september i Dallas.
Og tak til Ken Puls og Miguel Escobar, fordi jeg var nødt til at lave en time på strømforespørgsel, så selvfølgelig trak jeg den fantastiske bog ud, verdens største bog om Power Query. Jeg bladede gennem bogen, og jeg så, at de havde en teknik, og jeg sagde: ”Vent et øjeblik. Jeg vil se, om jeg kan tilpasse denne teknik. ” Og dette for mig er fantastisk i strømforespørgsel. Og her er hvad vi skal gøre, vi tager en strømforespørgsel, og så kører vi den strømforespørgsel, den forespørgsel, snesevis af gange, okay?
Og så var det eksempel, jeg kom på, hvor jeg ville hente data fra en webside, okay. Og jeg gik ud til - jeg ledte bare efter nogle websider, jeg kunne - som jeg kunne bruge som et eksempel. Jeg endte på Weather Underground, og her er URL'en, og du kan se, at jeg var i Dallas-Fort Worth, så vi trækker data til Dallas, og det ser ud til at være 2. januar 2015. Så lige der i URL'en er parametrene , ret? Og det er en URL, der bare er moden til at få denne ting til at fungere.
Vi kigger hurtigt på websiden, selvom det ikke er så vigtigt. Du kan se, at der er mange forskellige data herude på websiden, og jeg besluttede, at jeg bare skulle prøve at få nedbør og høj og lav temperatur. Og her er den forespørgsel. Og lad mig fortælle dig lige her, at denne podcast ikke handler om, hvordan du opretter denne forespørgsel, ny forespørgsel, fra andre kilder, fra internettet, angiv URL'en og derefter en masse trin, som jeg ikke vil detaljer her for at få min endelige svar på Max Temp, Min Temp og nedbør. Pointen er, at du vil tage din egen forespørgsel og få det til at fungere for en masse ting.
Så jeg klikker på Luk og indlæs, og denne forespørgsel fungerer, den returnerer min ene række. Alt er fantastisk. Og jeg kommer tilbage, jeg redigerer denne forespørgsel, og jeg går til View, Advanced Editor. Jeg vil tage denne forespørgsel, og jeg vil gøre det til en funktion, okay? Så lige her før ordet LET trykker jeg på Enter. Og i parentes vil jeg give den en variabel (MyURL) og derefter => lille pil der, okay? Fedt nok. Og så hernede, hvor de har URL'en i anførselstegn, vil jeg slippe af med hele URL'en inklusive citaterne og derefter skrive mit variabelnavn MyURL, okay. Så hvad vi siger er, at vi sender det til URL, og det vil udføre den samme forespørgsel, men uanset hvilken URL vi tilfældigvis sender den.
Nu, et par foruroligende ting her, når jeg klikker på Udført, åh mænd! Alle mine anvendte trin er væk, og de vil have mig til at indtaste en parameter. Bare ignorere alt det. Vi vil omdøbe dette; vi kalder det fxWeather. FX, selvfølgelig, som forkortelse for funktion, og du skal virkelig huske dette navn og huske, hvilke bogstaver der er store bogstaver, det bliver meget vigtigt om et par minutter. Hjem, Luk og indlæs og BAM! Alt er væk. Åh nej! Men det er okay. Okay, så vi ved, at det er der. Det er kun en forbindelse. Nu skal jeg komme herover, og dette er bare lige gammel Excel, okay? Så her er URL'en, jeg brød den ud i den første del af URL'en, den endelige del af URL'en. Jeg ved, at jeg skal tage datoen; Jeg har brug for at formatere det i dette underlige format af året,måned og dag, så jeg brugte TEKST-funktionen til at gøre det. Angiv startdatoen her. Jeg kan endda skifte lufthavn, så nu er jeg frem og tilbage. Lad os lave MCO for Orlando, og lad os lave nogle nylige data. Så jeg starter den 10/1/2016, okay. Så nu har vi opsat dette store lille bord her. Og forresten skal det være et bord. Du skal bruge format som tabel eller Ctrl + T. Så du ved, dette er bare at gribe den dato og derefter + 1 + 1 + 1. Jeg formaterer det, jeg bygger URL'en.Du skal bruge format som tabel eller Ctrl + T. Så du ved, dette er bare at gribe den dato og derefter + 1 + 1 + 1. Jeg formaterer det, jeg bygger URL'en.Du skal bruge format som tabel eller Ctrl + T. Så du ved, dette er bare at gribe den dato og derefter + 1 + 1 + 1. Jeg formaterer det, jeg bygger URL'en.
Okay nu, vi skal bygge en forespørgsel ud fra denne tabel. Okay, og der er mine oplysninger. Jeg vil tilføje en ny kolonne, tilføje en brugerdefineret kolonne, kolonnerne skulle kaldes vejr, og formlen vil være = fxWeather. Sørg for, at det er nøjagtigt det samme, de store og små bogstaver, og vi indsætter det felt, der hedder URL sådan, og lukker parentes. Ingen syntaksfejl. Klik på OK. De vil vide mere om fortrolighed her, dette er alle offentlige data, klik på Gem, okay. Så der er vores date. Det er sjovt, de har faktisk ændret mit format til noget, der ikke ligner det, jeg startede med. Og så er Vejret med udvidelsessymbolet. Så jeg kommer til at klikke på udvidelsessymbolet, fjern markeringen i Brug originalt kolonnenavn som præfiks. Jeg vil have Max, Min, Nedbør,klik på OK. Okay, og nu er alt, hvad jeg har brug for, datoen og de oplysninger derude. Så jeg skal højreklikke på og fjerne denne kolonne, højreklikke og fjerne denne kolonne. Herovre har jeg ikke brug for den tid, så jeg siger, det er bare en dato, okay. Og se hvad det laver hver dato, hvor jeg sender det; det vender tilbage til det høje, det lave og nedbøren for Orlando. Hver række her går ud til en anden webside. Forestil dig, at hvis det ikke var 15 rækker, men 5.000 rækker, ville du indstille det til at køre natten over. Jeg plejede at skrive makroer til dette. Faktisk er en af websiderne ude, hvordan man bygger en makro til at skrabe websider fra tusind forskellige websider på et websted, ikke længere nødvendigt med strømforespørgsel.Højreklik på og fjern denne kolonne, højreklik og fjern denne kolonne. Herovre har jeg ikke brug for den tid, så jeg siger, det er bare en dato, okay. Og se hvad det laver hver dato, hvor jeg sender det; det vender tilbage til det høje, det lave og nedbøren for Orlando. Hver række her går ud til en anden webside. Forestil dig, at hvis det ikke var 15 rækker, men 5.000 rækker, ville du indstille det til at køre natten over. Jeg plejede at skrive makroer til dette. Faktisk er en af websiderne ude, hvordan man bygger en makro til at skrabe websider fra tusind forskellige websider på et websted, ikke længere nødvendigt med strømforespørgsel.Højreklik på og fjern denne kolonne, højreklik og fjern denne kolonne. Herovre har jeg ikke brug for den tid, så jeg siger, det er bare en dato, okay. Og se hvad det laver hver dato, hvor jeg sender det; det vender tilbage til det høje, det lave og nedbøren for Orlando. Hver række her går ud til en anden webside. Forestil dig, at hvis det ikke var 15 rækker, men 5.000 rækker, ville du indstille det til at køre natten over. Jeg plejede at skrive makroer til dette. Faktisk er en af websiderne ude, hvordan man bygger en makro til at skrabe websider fra tusind forskellige websider på et websted, ikke længere nødvendigt med strømforespørgsel.s returnerer det høje, det lave og nedbøren til Orlando. Hver række her går ud til en anden webside. Forestil dig, at hvis det ikke var 15 rækker, men 5.000 rækker, ville du indstille det til at køre natten over. Jeg plejede at skrive makroer til dette. Faktisk er en af websiderne ude, hvordan man bygger en makro til at skrabe websider fra tusind forskellige websider på et websted, ikke længere nødvendigt med strømforespørgsel.s returnerer det høje, det lave og nedbøren til Orlando. Hver række her går ud til en anden webside. Forestil dig, at hvis det ikke var 15 rækker, men 5.000 rækker, ville du indstille det til at køre natten over. Jeg plejede at skrive makroer til dette. Faktisk er en af websiderne, hvordan man bygger en makro til at skrabe websider fra tusind forskellige websider på et websted, ikke længere nødvendigt med strømforespørgsel.
Nu, når jeg lukker og indlæser, er det sjovt, at eksemplet her viser mig alle resultaterne. Når jeg lukker og indlæser, vil de faktisk gøre hver forespørgsel. Og så lige nu har vi indlæst preview-rækker, og det vil tage lang tid, før disse oplysninger opdateres. Så gå til frokost, gør noget, især hvis du laver mere end 15 rækker. Og det er sjovt, at forhåndsvisning er korrekt, men de går stadig ud og kunne klumpe, kunne klumpe, kunne klumpe igennem hver enkelt række.
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 eksempelfilen her: Podcast2056.xlsm
