Power Query je dosť silný. Ale chystám sa urobiť Power Query na druhú … napísať dopyt na jednu stránku a potom nechať program Excel vykonať rovnaký dopyt pre celý zoznam webových stránok.
Pozeraj video
- Dnešný trik je adaptovaný z knihy M is for Data Monkey
- Vytvorte dopyt, aby ste získali údaje z jednej webovej stránky
- Upravte dotaz, aby ste ho zmenili na funkciu pomocou (VariableName) => pred Let
- Zmeňte napevno zadanú URL na VariableName
- Premenujte dopyt na fxWeather
- Zatvoriť a načítať. Údaje zmiznú.
- Pomocou trikov programu Excel vytvorte tabuľku všetkých adries URL
- Vytvorte dotaz z tejto tabuľky.
- Pridajte nový stĺpec Počasie
=fxWeather((URL)) - Rozbaľte stĺpec. Zrušte začiarknutie položky Predpona
- Úžasný!
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2056: Power Query na druhú
Ahoj, vitaj späť na netcastu, som Bill Jelen. Na tento trik som narazil, keď som sa pripravoval na seminár na konferencii v Dallase s názvom Excelapalooza, najväčší názov konferencie Excel vôbec. Mali by ste si to pozrieť každý september v Dallase.
A vážim si Kena Pulsa a Miguela Escobara, pretože som musel hodinu venovať otázkam napájania, takže som samozrejme vytiahol úžasnú knihu, najväčšiu knihu na svete o Power Query. Listoval som v knihe a videl som, že majú jednu techniku, a povedal som: „Počkaj chvíľu. Uvidím, či dokážem túto techniku prispôsobiť. “ A toto je pre mňa úžasné v otázke moci. A tu je to, čo urobíme, vezmeme jeden mocninový dotaz a potom ten mocninový dotaz, ten dotaz, spustíme desiatkykrát, dobre?
Takže príklad, ktorý som vymyslel, bol ten, kde som chcel získať údaje z webovej stránky, dobre. A išiel som do - len som hľadal nejaké webové stránky, ktoré by som mohol -, ktoré by som mohol použiť ako príklad. Skončil som v Weather Underground a tu je adresa URL. Môžete vidieť, že som bol v Dallas-Fort Worth, takže sťahujeme údaje pre Dallas a vyzerá to ako 2. januára 2015. Takže priamo v adrese URL sú parametre , správny? A to je adresa URL, ktorá je práve zrelá na to, aby táto vec fungovala.
Rýchlo sa pozrieme na webovú stránku, aj keď to nie je také dôležité. Uvidíte, že na webovej stránke je veľa rôznych údajov a rozhodol som sa, že sa len pokúsim získať zrážky a vysokú a nízku teplotu. A tu je ten dopyt. A práve tu vám poviem, že tento podcast nie je o tom, ako vytvoriť tento dopyt, nový dopyt, z iných zdrojov, z webu, určiť adresu URL a potom kopu krokov, ktoré tu nebudem podrobne rozoberať, aby som získal svoje konečná odpoveď na maximálnu teplotu, minimálnu teplotu a zrážky. Ide o to, že si vezmete svoj vlastný dotaz a urobíte z neho kopu vecí.
Takže kliknem na Zavrieť a načítať a tento dopyt funguje, vracia mi jeden riadok. Všetko je úžasné. A vrátim sa späť, upravím tento dotaz a prejdem do časti Zobraziť, Pokročilý editor. Zoberiem tento dopyt a urobím z neho funkciu, dobre? Takže práve tu pred slovom LET stlačím kláves Enter. A v zátvorke tomu dám premennú (MyURL) a potom => malá šípka tam, dobre? V pohode A potom sem dolu, kde majú URL v úvodzovkách, sa chcem zbaviť celej URL vrátane úvodzoviek a potom napísať môj premenný názov MyURL, v poriadku. Takže čo hovoríme je, pošleme to na URL a urobí to ten istý dopyt, ale s akoukoľvek URL to náhodou odovzdáme.
Teraz tu vidím pár znepokojujúcich vecí, keď kliknem na Hotovo, ach, muži! Všetky moje použité kroky sú preč a chcú, aby som zadal parameter. Toto všetko jednoducho ignorujte. Premenujeme to; budeme to volať fxWeather. Pretože FX je skratka pre funkciu a musíte si skutočne pamätať toto meno a pamätať si, ktoré písmená sú veľké, bude to za pár minút veľmi dôležité. Domov, Zatvoriť a načítať a BAM! Všetko je preč. Ale nie! Ale to je v poriadku. Dobre, takže vieme, že to tam je. Je to iba spojenie. Teraz prídem sem a toto je proste starý Excel, dobre? Takže tu je adresa URL, prelomil som ju do prvej časti adresy URL, do koncovej časti adresy URL. Viem, že si musím vziať rande; Potrebujem to naformátovať do tohto zvláštneho formátu roku,mesiac a deň, tak som na to použil funkciu TEXT. Sem zadajte počiatočný dátum. Môžem dokonca zmeniť letisko, takže teraz som tam a späť. Urobme MCO pre Orlando a urobme niekoľko posledných údajov. Takže začnem 1. 10. 2016, v poriadku. Takže teraz tu máme pripravený tento skvelý malý stolík. A mimochodom, musí to byť stôl. Musíte použiť formát ako tabuľku alebo Ctrl + T. Takže, viete, toto je iba uchytenie toho dátumu a potom + 1 + 1 + 1. Naformátujem to, zostavím URL.Musíte použiť formát ako tabuľku alebo Ctrl + T. Takže, viete, toto je iba uchytenie toho dátumu a potom + 1 + 1 + 1. Naformátujem to, zostavím URL.Musíte použiť formát ako tabuľku alebo Ctrl + T. Takže, viete, toto je iba chytenie toho dátumu a potom + 1 + 1 + 1. Naformátujem to, zostavím URL.
Dobre, teraz vytvoríme dopyt z tejto tabuľky. Dobre, a sú tu moje informácie. Chystám sa pridať nový stĺpec, pridať vlastný stĺpec, stĺpce sa budú volať Počasie a vzorec bude = fxWeather. Uistite sa, že ide o úplne rovnaké veľké a malé písmená, a do tohto poľa s názvom URL vložíme podobné závorky. Žiadne syntaktické chyby, kliknite na tlačidlo OK. Chcú tu vedieť o ochrane osobných údajov, toto sú všetky verejné údaje, kliknite na tlačidlo Uložiť, dobre. Takže, je tu naše rande. Je zábavné, že môj formát zmenili na niečo, čo nevyzerá ako to, s čím som začínal. A potom je tu Počasie so symbolom rozbalenia. Kliknem teda na symbol rozbalenia, zruším začiarknutie políčka Použiť ako predponu pôvodný názov stĺpca. Chcem Max, Min, Zrážky,kliknite na OK. Dobre, a teraz už potrebujem iba dátum a tieto informácie. Kliknite teda pravým tlačidlom myši a odstránim tento stĺpec, pravým tlačidlom myši a odstránim tento stĺpec. Tu nepotrebujem ten čas, takže poviem, že toto je iba rande, dobre. A uvidíš, čo robí každé rande, keď to absolvujem; vracia vysoké, nízke a zrážky pre Orlando. Každý riadok tu smeruje na inú webovú stránku. Len si predstavte, keby to nebolo 15 riadkov, ale 5 000 riadkov, nastavili by ste ho tak, aby fungoval cez noc. Kedysi som na to písal makra. Jednou z webových stránok v skutočnosti je, ako vytvoriť makro na zoškrabanie webových stránok z tisíca rôznych webových stránok na webe, čo už nie je potrebné pomocou dotazu na výkon.ll kliknite pravým tlačidlom myši a odstráňte tento stĺpec, kliknite pravým tlačidlom myši a odstráňte tento stĺpec. Tu nepotrebujem ten čas, takže poviem, že toto je iba rande, dobre. A uvidíš, čo robí každé rande, keď to absolvujem; vracia vysoké, nízke a zrážky pre Orlando. Každý riadok tu smeruje na inú webovú stránku. Len si predstavte, keby to nebolo 15 riadkov, ale 5 000 riadkov, nastavili by ste ho tak, aby fungoval cez noc. Kedysi som na to písal makrá. Jednou z webových stránok v skutočnosti je, ako vytvoriť makro na zoškrabanie webových stránok z tisíca rôznych webových stránok na webe, čo už nie je potrebné pomocou dotazu na výkon.ll kliknite pravým tlačidlom myši a odstráňte tento stĺpec, kliknite pravým tlačidlom myši a odstráňte tento stĺpec. Tu nepotrebujem ten čas, takže poviem, že toto je iba rande, dobre. A uvidíš, čo robí každé rande, keď to absolvujem; vracia vysoké, nízke a zrážky pre Orlando. Každý riadok tu smeruje na inú webovú stránku. Len si predstavte, keby to nebolo 15 riadkov, ale 5 000 riadkov, nastavili by ste ho tak, aby fungoval cez noc. Kedysi som na to písal makrá. Jednou z webových stránok v skutočnosti je, ako vytvoriť makro na zoškrabanie webových stránok z tisíca rôznych webových stránok na webe, čo už nie je potrebné pomocou dotazu na výkon.s vracia vysoké, nízke a zrážky pre Orlando. Každý riadok tu smeruje na inú webovú stránku. Len si predstavte, keby to nebolo 15 riadkov, ale 5 000 riadkov, nastavili by ste ho tak, aby fungoval cez noc. Kedysi som na to písal makra. Jednou z webových stránok v skutočnosti je, ako vytvoriť makro na zoškrabanie webových stránok z tisíca rôznych webových stránok na webe, čo už nie je potrebné pomocou dotazu na výkon.s vracia vysoké, nízke a zrážky pre Orlando. Každý riadok tu smeruje na inú webovú stránku. Len si predstavte, keby to nebolo 15 riadkov, ale 5 000 riadkov, nastavili by ste ho tak, aby fungoval cez noc. Kedysi som na to písal makra. Jednou z webových stránok v skutočnosti je, ako vytvoriť makro na zoškrabanie webových stránok z tisíca rôznych webových stránok na webe, čo už nie je potrebné pomocou dotazu na výkon.
Teraz, keď zavriem a načítam, je zábavné, že tu ukážka zobrazuje všetky výsledky. Keď zavriem a načítam, skutočne idú robiť každý dotaz. Takže teraz máme načítané riadky ukážky a bude trvať dosť dlho, kým sa tieto informácie aktualizujú. Takže choďte na obed, urobte niečo, najmä ak robíte viac ako 15 riadkov. A je sranda, že ukážka je správna, ale stále chodia von a môžu chunkovať, chunkovať, chunkovať cez každý jednotlivý riadok.
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
Stiahnutie vzorového súboru nájdete tu: Podcast2056.xlsm
