Power Query je nový nástroj od společnosti Microsoft pro extrakci, transformaci a načítání dat. Dnešní článek pojednává o zpracování všech souborů ve složce.
Power Query je integrován do aplikace Excel 2016 a je k dispozici ke stažení zdarma v určitých verzích aplikace Excel 2010 a Excel 2013. Nástroj je navržen tak, aby extrahoval, transformoval a načítal data do aplikace Excel z různých zdrojů. Nejlepší část: Power Query si pamatuje vaše kroky a bude je přehrávat, když budete chtít aktualizovat data. Jak tato kniha přejde do tisku, funkce Power Query v aplikaci Excel 2016 jsou na kartě Data ve skupině Získat a transformovat v části Nový dotaz. Je těžké předvídat, zda Microsoft zpětně přejmenuje Power Query na Get & Transform v Excel 2010 a Excel 2013.
Tento bezplatný doplněk je tak úžasný, že by o něm mohla být celá kniha. Ale jako jeden z mých nejlepších 40 tipů chci pokrýt něco velmi jednoduchého: uvedení seznamu souborů do aplikace Excel spolu s datem vytvoření souboru a možná velikostí. To je užitečné pro vytvoření seznamu sešitů rozpočtu nebo seznamu fotografií.
V aplikaci Excel 2016 vyberete Data, Nový dotaz, Ze souboru, Ze složky. V dřívějších verzích aplikace Excel použijte Power Query, ze souboru, ze složky. Zadejte složku:
Při úpravách dotazu klikněte pravým tlačítkem na všechny sloupce, které nechcete, a zvolte Odebrat.
Chcete-li získat velikost souboru, klikněte na tuto ikonu ve sloupci Atributy:
Zobrazí se seznam dalších atributů. Zvolte velikost.
K dispozici je velký seznam možností transformace.
až budete hotovi s úpravou dotazu, klikněte na Zavřít a načíst.
Data se načtou do aplikace Excel jako tabulka.
Později pro aktualizaci tabulky vyberte Data, Obnovit vše. Excel si pamatuje všechny kroky a aktualizuje tabulku o aktuální seznam souborů ve složce.
Chcete-li získat úplný popis funkce dříve známé jako Power Query, podívejte se na M is for (Data) Monkey od Ken Puls a Miguel Escobar.
M je pro (DATA) MONKEY »
Děkujeme Miguelovi Escobarovi, Robovi Garciovi, Mikeu Girvinovi, Rayu Hauserovi a Colinovi Michaelovi za nominaci na Power Query.
Sledovat video
- Nástroje Power Query jsou na kartě Data v aplikaci Excel 2016
- Doplněk zdarma pro roky 2010 a 2013
- Seznam všech souborů ze složky do mřížky aplikace Excel pomocí Power Query
- Vyberte Nový dotaz, ze souboru, ze složky
- Není to zřejmé: rozbalte pole atributu a získejte velikost
- Pokud jsou vaše data v souborech CSV, můžete importovat všechny soubory najednou do jedné mřížky
- Propagujte řádek záhlaví
- Odstraňte zbývající řádky záhlaví
- Nahraďte „“ nulou
- Vyplňte dolů pro zobrazení osnovy
- Odstraňte sloupec celkového součtu
- Zrušit otočení dat
- Vzorec pro převod názvů měsíců na data
- Kompletní seznam kroků - největší Zpět na světě
- Následující den - obnovte dotaz a proveďte všechny kroky znovu
Přepis videa
- Power Query je integrován do verzí Windows Excel 2016. Podívejte se na kartu Data ve skupině Získat a transformovat. Pokud máte rok 2010 nebo
- 2013, pokud používáte Windows
- a ne Mac vše, co je zde v Get & Transform
- si můžete zdarma stáhnout z Microsoftu. Stačí hledat
- Stáhněte si Power Query.
- Dnes mám zájem o získání seznamu souborů pomocí Power Query. Já
- chcete zobrazit seznam všech souborů ve složce.
- Možná potřebuji zjistit, které soubory jsou
- velké soubory nebo potřebuji třídit nebo potřebuji
- víte, že dostanete kombinaci vás
- znát rozpočtové soubory, které jsme rozeslali
- a pak jinou složku
- vrátili jsme se.
- Začněte tím, že přejdete na Data, Získat a transformovat, Ze souboru, Ze složky.
- Vložte cestu ke složce nebo použijte tlačítko Procházet.
- Klikněte na OK a zobrazí mi to
- náhled. Zvolte Upravit.
- Pár věcí, které tady vidíte, máme
- název souboru přípona datum
- zpřístupněno, datum upraveno, datum vytvořeno.
- Opravdu není zřejmé, že tento symbol vedle nadpisu Atributy znamená Rozbalit. Klikněte na tento symbol a je v něm více věcí
- zde a pokud kliknete na tento symbol, pak já
- může vstoupit a získat věci jako velikost souboru
- nebo pokud je to jen pro čtení a podobné věci
- že v tomto případě chci pouze soubor
- velikost. Vyberte velikost souboru. Klikněte na OK. Dají vám nové pole s názvem Attributes.Size.
- Vidím, kolik bytů je uvnitř
- každý soubor.
- Možná tu nepotřebuji všechno
- Nepotřebuji datum vytvoření, abych mohl
- klikněte pravým tlačítkem a řekněte, že chci
- odstraňte tento sloupec. Tento
- binární nepotřebuji to odstraní
- ten sloupec. Na pásu karet klikněte na Zavřít a načíst.
- Za pár sekund budete mít k dispozici pohled na
- vše v této složce, pokud je složka
- změny, které sem můžu přijít a můžu
- obnovte dotaz a vrátí se zpět
- ven a vytáhnout ta data správně to je
- pro mě je to problém, na který jsme byli zvyklí
- mít pořád čas, abychom rozeslali 200
- soubory rozpočtu
- a někoho dostanete zpět, ne všechny
- zpět musíte být schopni to srovnat
- teď mohu v podstatě udělat vlookup
- mezi složkami.
- Je úžasné, jak
- v pohodě to je, ale podívejme se dál
- what I have in the book and show you how
- that's just the tip of the iceberg.
- I'm going to create another query. Data, New Query, From File, From Folder.
- I'll copy that folder path here.
- click edit.
- As of October 2016, this trick only works with CSV
- files, but in 2017 it was updated to work with single-sheet Excel files. I
- have a folder a whole bunch of files and
- I want to create one excel grid with all
- of the data from all of these files.
- It's not intuitive at all. Look next to the heading for the Binary column. There is an icon with two arrows pointing down at a horizontal line.
- Click that.
- BAM! it just pulled in every single record from
- every single file in that folder!
- Isn't
- that amazing I mean that was a VBA macro
- before and it takes months to learn VBA
- macros you can learn power query in ten
- minutes.
- We have to select this column and
- go to replace values say that we're
- going to
- replace nothing with the word null click
- okay
- That'll give us Nulls in place of empty cells.
- Those nulls allow us to use this amazing
- featured called Fill Down. Watch that
- column when I choose Fill Down. BAM it
- just pulled in all of that outline view
- and brought the value down.
- I don't need the Grand Total column.
- Right-click and remove.
- Now at this point you say oh yeah hey we could
- pull this in and it'd be awesome. But if
- we wanted to create a pivot table from
- this data having a repeating group going
- across Jan Feb Mar is not a good format
- for a pivot tables.
- Right now we have 47
- rows I need to have 47 times twelve rows
- and to do this in a regular Excel file
- it is horrendous using a Multiple
- Consolidation Range that I learned from
- Mike Alexander at Data Pig Technologies.
- But it is easy in Power Query. Check this out I'm going to choose the
- label columns along the left. These are the things that I don't
- want to change and then on the Transform tab, choose Unpivot Other Columns.
- We go from 47 rows to 564 rows
- that's an amazing step.
- Here you can see
- that these values are text. It is easy enough to
- change it to either currency or a whole
- number. Right click the heading and choose Rename and call it
- revenue
- How about these months? They're
- all text such as Jan, Feb, Mar. Here's an awesome way to fix
- that we go to add column add a custom
- column doesn't matter what the name you use.
- The calculation, in quotes, is " 1, 2016". Click OK.
- Now we have this new custom column I'm going to take the
- attribute column containing Month names and the new custom column. Select both columns
- and say I want to merge those columns
- with a space in between and call it date.
- Click OK. That looks enough
- like a real date that when I go to
- transform and change it to a date it
- converts it to a true Excel date.
- At this point these two temporary
- columns I can right click and remove.
- Now you could be saying to yourself:
- Wait, Bill! we could have done all of this in Excel and that's absolutely true we could have done all of it in Excel it would have been harder to get all the CSV files into one file it would have taken longer to fill in the blanks it definitely would have taken longer to do the unpivot operation but here's the thing look over on the right-hand side we haven't talked about Applied Steps at all. The Applied Steps panel is like the world's greatest undo if you need an audit trail if the auditors come and say well how did you get from all these CSV files to this file that we're building our financial statements on you can go back and show what it looked like at each step along the way. If you screwed something up back here you could change or edit that step. Next, on the Power Query Home tab, choose Close and Load. So here's our data set this is based on all of the files in this folder. Let's build a little pivot table from here insert pivot table existing worksheet right here and I'll put revenue in the values area products down the left hand side you see that we have six million in revenue. Well that's today's data now tomorrow tomorrow let's say that we get a couple of new customers a couple of new files come along and our IT department takes those and dumps them into our folder all I have to do is reopen this file select the query come over here and refresh and then come here analyze refresh the pivot table and we have the new data! Power Query is faster on day one maybe by a factor of 20-30%. On day two is faster by 99% it's an absolutely amazing product Power Query. It's in Excel 2016 but if you're in 2010 or 2013 for Windows you're more than welcome to go out and download it for free. The book that will teach you about Power Query is M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
- Tato kniha bude učit
- vám všechno o moc dotazu
- rozhraní je to úžasná kniha nejlepší
- kniha o dotazu na energii vše, co jsem se naučil
- Poučil jsem se z této knihy. Letěl jsem z
- Orlando do Dallasu - četl jsem celou knihu
- a moje znalost dotazu na moc
- vyletěl za dvě hodiny, můžete být až
- rychlost a nahradit věci, které byste
- měli zvyklý dělat s VBA.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2037.xlsx
