Power Query je docela silný. Ale chystám se udělat Power Query Squared… napsat dotaz na jednu stránku a poté nechat Excel provést stejný dotaz pro celý seznam webových stránek.
Sledovat video
- Dnešní trik je upraven z knihy M is for Data Monkey
- Vytvořte dotaz a získejte data z jedné webové stránky
- Upravte dotaz a změňte jej na funkci pomocí (VariableName) => před Let
- Změňte pevně zadanou adresu URL na VariableName
- Přejmenujte dotaz na fxWeather
- Zavřít a načíst. Data zmizí.
- Pomocí triku Excel vytvořte tabulku všech adres URL
- Vytvořte dotaz z této tabulky.
- Přidejte nový sloupec Počasí
=fxWeather((URL)) - Rozbalte sloupec. Zrušte zaškrtnutí předpony
- Úžasný!
Přepis videa
Naučte se Excel z Podcastu, epizoda 2056: Power Query na druhou
Hej, vítej zpět na netcastu, jsem Bill Jelen. Na tento trik jsem narazil, když jsem se připravoval na seminář na konferenci v Dallasu s názvem Excelapalooza, největší název konference Excel vůbec. Měli byste si to prohlédnout každé září v Dallasu.
A uznání Kenu Pulsovi a Miguelovi Escobarovi, protože jsem musel hodinu zkoumat moc, takže jsem samozřejmě vytáhl úžasnou knihu, největší knihu na světě o Power Query. Listoval jsem knihou a viděl jsem, že mají jednu techniku, a řekl jsem: „Počkej chvíli. Uvidím, jestli dokážu tuto techniku přizpůsobit. “ A to je pro mě v dotazu na výkon úžasné. A tady je to, co uděláme, vezmeme jeden mocninový dotaz a potom ten mocninový dotaz, ten dotaz, spustíme desítkykrát, dobře?
Takže příklad, který jsem přišel, byl ten, kde jsem chtěl vytáhnout data z webové stránky, dobře. A šel jsem ven - jen jsem hledal nějaké webové stránky, které jsem mohl -, které bych mohl použít jako příklad. Skončil jsem v Weather Underground a tady je URL a můžete vidět, že jsem byl v Dallas-Fort Worth, takže stahujeme data pro Dallas a vypadá to jako 2. ledna 2015. Takže právě tam v URL jsou parametry , že jo? A to je adresa URL, která je právě zralá pro to, aby tato věc fungovala.
Rychle se podíváme na webovou stránku, i když to není tak důležité. Uvidíte, že na webové stránce je spousta různých dat a já jsem se rozhodl, že se jen pokusím získat srážky a vysokou a nízkou teplotu. A tady je ten dotaz. A hned vám řeknu, že tento podcast není o tom, jak vytvořit tento dotaz, nový dotaz, z jiných zdrojů, z webu, zadat adresu URL a pak spoustu kroků, které zde nebudu podrobně popisovat konečná odpověď na maximální teplotu, minimální teplotu a srážky. Jde o to, že si vezmete svůj vlastní dotaz a uděláte to pro spoustu věcí.
Takže kliknu na Zavřít a načíst a tento dotaz funguje, vrací mi jeden řádek. Všechno je úžasné. Vrátím se zpět, upravím tento dotaz a přejdu do zobrazení Pokročilý editor. Vezmu si tento dotaz a udělám z něj funkci, dobře? Takže tady před slovem LET stisknu Enter. A v závorkách tomu dám proměnnou (MyURL) a pak => malá šipka tam, dobře? Chladný. A pak tady dole, kde mají URL v uvozovkách, se chci zbavit celé URL včetně uvozovek a potom zadejte můj název proměnné MyURL, dobře. Takže to, co říkáme, je, že to předáme URL a bude to dělat stejný dotaz, ale s jakoukoli URL to náhodou předáme.
Tady pár znepokojujících věcí, když kliknu na Hotovo, muži! Všechny moje použité kroky jsou pryč a chtějí, abych zadal parametr. Prostě to všechno ignorujte. Přejdeme to přejmenovat; budeme to říkat fxWeather. FX, samozřejmě jako zkratka pro funkci a musíte si opravdu pamatovat toto jméno a pamatovat si, která písmena jsou velká, to bude za pár minut velmi důležité. Domů, Zavřít a načíst a BAM! Všechno je pryč. Ach ne! Ale to je v pořádku. Dobře, takže víme, že to tam je. Je to jen spojení. Teď půjdu sem a tohle je prostě starý Excel, dobře? Tady je adresa URL, rozdělil jsem ji na první část adresy URL, koncovou část adresy URL. Vím, že musím vzít datum; Potřebuji to naformátovat v tomto podivném formátu roku,měsíc a den, tak jsem k tomu použil funkci TEXT. Sem zadejte počáteční datum. Dokonce mohu změnit letiště, takže teď jsem tam a zpět. Udělejme MCO pro Orlando a udělejme několik posledních údajů. Takže začnu 1. 10. 2016, dobře. Takže teď tu máme tento skvělý malý stůl. A mimochodem, musí to být stůl. Musíte použít formát jako tabulku nebo Ctrl + T. Víte, tohle je jen popadnutí toho data a pak + 1 + 1 + 1. Naformátuji to, vytvořím URL.Musíte použít formát jako tabulku nebo Ctrl + T. Víte, tohle je jen popadnutí toho data a pak + 1 + 1 + 1. Naformátuji to, vytvořím URL.Musíte použít formát jako tabulku nebo Ctrl + T. Víte, tohle je jen popadnutí toho data a pak + 1 + 1 + 1. Naformátuji to, vytvořím URL.
Dobře, teď vytvoříme dotaz z této tabulky. Dobře, a jsou tu moje informace. Chystám se přidat nový sloupec, přidat vlastní sloupec, sloupce se budou jmenovat Počasí a vzorec bude = fxWeather. Ujistěte se, že se jedná o přesně stejná velká a stejná velká a malá písmena, a vložíme toto pole s názvem URL tak, abychom uzavřeli závorky. Žádné chyby syntaxe, klikněte na OK. Chtějí zde vědět o ochraně osobních údajů, to jsou všechna veřejná data, klikněte na Uložit, dobře. Takže je tu naše rande. Je legrační, že ve skutečnosti změnili můj formát na něco, co nevypadá jako to, s čím jsem začínal. A pak je zde Počasí se symbolem rozbalení. Takže kliknu na symbol rozbalení, zruším zaškrtnutí Použít jako předponu původní název sloupce. Chci Max, Min, Srážení,klikněte na OK. Dobře, a teď potřebuji jen datum a ty informace. Takže kliknu pravým tlačítkem a odstraním tento sloupec, kliknu pravým tlačítkem a odstraním tento sloupec. Tady, ten čas nepotřebuji, takže řeknu, že je to jen rande, dobře. A uvidíte, co to dělá každé rande, když to předávám; vrací vysoké, nízké a srážky pro Orlando. Každý řádek zde vychází na jinou webovou stránku. Představte si, že kdyby to nebylo 15 řádků, ale 5 000 řádků, nastavili byste to tak, aby fungovalo přes noc. K tomu jsem psal makra. Ve skutečnosti je jednou z webových stránek to, jak vytvořit makro pro škrábání webových stránek z tisíce různých webových stránek na webu, což již není nutné s dotazem na výkon.ll klikněte pravým tlačítkem a odeberte tento sloupec, klikněte pravým tlačítkem a odeberte tento sloupec. Tady, ten čas nepotřebuji, takže řeknu, že je to jen rande, dobře. A uvidíte, co to dělá každé rande, když to předávám; vrací vysoké, nízké a srážky pro Orlando. Každý řádek zde vychází na jinou webovou stránku. Představte si, že kdyby to nebylo 15 řádků, ale 5 000 řádků, nastavili byste to tak, aby fungovalo přes noc. Dříve jsem k tomu psal makra. Ve skutečnosti je jednou z webových stránek to, jak vytvořit makro pro škrábání webových stránek z tisíce různých webových stránek na webu, což již není nutné s dotazem na výkon.ll klikněte pravým tlačítkem a odeberte tento sloupec, klikněte pravým tlačítkem a odeberte tento sloupec. Tady, ten čas nepotřebuji, takže řeknu, že je to jen rande, dobře. A uvidíte, co to dělá každé rande, když to předávám; vrací vysoké, nízké a srážky pro Orlando. Každý řádek zde vychází na jinou webovou stránku. Představte si, že kdyby to nebylo 15 řádků, ale 5 000 řádků, nastavili byste to tak, aby fungovalo přes noc. K tomu jsem psal makra. Ve skutečnosti je jednou z webových stránek to, jak vytvořit makro pro škrábání webových stránek z tisíce různých webových stránek na webu, což již není nutné s dotazem na výkon.Vrací vysoké, nízké a srážky pro Orlando. Každý řádek zde vychází na jinou webovou stránku. Představte si, že kdyby to nebylo 15 řádků, ale 5 000 řádků, nastavili byste to tak, aby fungovalo přes noc. Dříve jsem k tomu psal makra. Ve skutečnosti je jednou z webových stránek to, jak vytvořit makro pro škrábání webových stránek z tisíce různých webových stránek na webu, což již není nutné s dotazem na výkon.Vrací vysoké, nízké a srážky pro Orlando. Každý řádek zde vychází na jinou webovou stránku. Představte si, že kdyby to nebylo 15 řádků, ale 5 000 řádků, nastavili byste to tak, aby fungovalo přes noc. Dříve jsem k tomu psal makra. Ve skutečnosti je jednou z webových stránek to, jak vytvořit makro pro škrábání webových stránek z tisíce různých webových stránek na webu, což již není nutné s dotazem na výkon.
Nyní, když zavírám a načítám, je zábavné, že zde náhled zobrazuje všechny výsledky. Když zavřu a načtou, skutečně se chystají udělat každý dotaz. Takže právě teď máme načtené řádky náhledu a bude trvat dlouhou dobu, než se tyto informace aktualizují. Takže jděte na oběd, udělejte něco, zvláště pokud děláte více než 15 řádků. A je legrační, že náhled je správný, ale stále chodí ven a mohou se chunkovat, chunkovat, chunkovat se v každém jednotlivém řádku.
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
Stáhněte si ukázkový soubor zde: Podcast2056.xlsm
