Power Query je nový nástroj od spoločnosti Microsoft na extrakciu, transformáciu a načítanie údajov. Dnešný článok je o spracovaní všetkých súborov v priečinku.
Power Query je zabudovaný do Excelu 2016 a je k dispozícii na stiahnutie zadarmo v určitých verziách Excel 2010 a Excel 2013. Tento nástroj je určený na extrakciu, transformáciu a načítanie údajov do Excelu z rôznych zdrojov. Najlepšia časť: Power Query si pamätá vaše kroky a prehrá ich, keď chcete obnoviť dáta. Keď bude táto kniha v tlači, funkcie Power Query v programe Excel 2016 sa nachádzajú na karte Údaje v skupine Získanie a transformácia v časti Nový dopyt. Je ťažké predpovedať, či Microsoft spätne premenuje Power Query na Get & Transform v Excel 2010 a Excel 2013.
Tento bezplatný doplnok je taký úžasný, že by o ňom mohla byť celá kniha. Ale ako jeden z mojich 40 najlepších tipov by som chcel uviesť niečo veľmi jednoduché: priniesť zoznam súborov do Excelu spolu s dátumom vytvorenia súboru a možno aj veľkosťou. To je užitočné pri vytváraní zoznamu zošitov s rozpočtom alebo zoznamu fotografií.
V programe Excel 2016 vyberiete Údaje, Nový dopyt, Zo súboru, Z priečinka. V starších verziách programu Excel použite Power Query, zo súboru, zo priečinka. Zadajte priečinok:
Pri úprave dotazu kliknite pravým tlačidlom myši na ľubovoľné stĺpce, ktoré nechcete a zvoľte Odstrániť.
Veľkosť súboru zobrazíte kliknutím na túto ikonu v stĺpci Atribúty:
Zobrazí sa zoznam ďalších atribútov. Vyberte veľkosť.
K dispozícii je veľký zoznam možností transformácie.
až budete hotoví s úpravou dotazu, kliknite na Zavrieť a načítať.
Údaje sa načítajú do programu Excel ako tabuľka.
Neskôr budete môcť tabuľku aktualizovať výberom položky Údaje, Obnoviť všetko. Excel si pamätá všetky kroky a aktualizuje tabuľku aktuálnym zoznamom súborov v priečinku.
Kompletný popis funkcie predtým známej ako Power Query nájdete v dokumente M is for (Data) Monkey od Kena Pulsa a Miguela Escobara.
M je pre (DATA) MONKEY »
Ďakujeme Miguelovi Escobarovi, Robovi Garciovi, Mikeovi Girvinovi, Rayovi Hauserovi a Colinovi Michaelovi za nomináciu programu Power Query.
Pozeraj video
- Nástroje Power Query sú na karte Údaje v programe Excel 2016
- Doplnok zadarmo pre roky 2010 a 2013
- Zoznam všetkých súborov z priečinka do mriežky Excel pomocou Power Query
- Vyberte Nový dopyt, zo súboru, zo priečinka
- Nie je to zrejmé: rozšírením poľa atribútu získate veľkosť
- Ak sú vaše údaje v súboroch CSV, môžete importovať všetky súbory naraz do jednej mriežky
- Propagujte riadok nadpisu
- Odstráňte zostávajúce riadky hlavičky
- Nahraďte „“ nulou
- Vyplňte nadol pre obrysový pohľad
- Odstráňte stĺpec celkového súčtu
- Zrušiť otočenie údajov
- Vzorec na prevod názvov mesiacov na dátumy
- Kompletný zoznam krokov - najväčší Undo na svete
- Nasledujúci deň - obnovením dotazu vykonajte všetky kroky znova
Prepis videa
- Power Query je zabudovaný do verzií systému Excel 2016 pre Windows. Pozrite sa na kartu Údaje v skupine Získať a transformovať. Ak máte rok 2010 resp
- 2013, pokiaľ používate systém Windows
- a nie Mac všetko, čo je tu v Get & Transform
- si môžete zadarmo stiahnuť z Microsoftu. Stačí hľadať
- Stiahnite si Power Query.
- Dnes by ma zaujímalo použitie programu Power Query na získanie zoznamu súborov. Ja
- chcete vypísať všetky súbory v priečinku.
- Možno potrebujem zistiť, ktoré súbory sú
- veľké súbory alebo potrebujem triediť alebo potrebujem
- viete, že dostanete kombináciu vás
- poznať rozpočtové súbory, ktoré sme rozposlali
- a potom iný priečinok
- sme sa vrátili.
- Ak chcete začať, prejdite do prehľadov Data, Get & Tranform, From File, From Folder.
- Vložte cestu k priečinku alebo použite tlačidlo Prehľadávať.
- Kliknite na OK a oni mi to ukážu
- Náhľad. Vyberte možnosť Upraviť.
- Tu vidíte pár vecí, ktoré tu máme
- názov súboru prípona dátum
- sprístupnené, dátum zmenený, dátum vytvorenia.
- Naozaj nie je zrejmé, že tento symbol vedľa nadpisu Atribúty znamená Rozbaliť. Kliknite na tento symbol a je v ňom viac vecí
- tu a ak kliknete na tento symbol, potom ja
- môže vstúpiť a získať napríklad veľkosť súboru
- alebo ak je to iba na čítanie a podobné veci
- že v takom prípade chcem iba súbor
- veľkosť. Vyberte veľkosť súboru. Kliknite na ok. Dajú vám nové pole s názvom Atribúty. Veľkosť.
- Vidím, koľko bajtov je v
- každý súbor.
- Možno tu nemusím všetko možno
- Nepotrebujem vytvorený dátum, aby som mohol
- kliknite pravým tlačidlom myši a povedzte, že chcem
- odstráňte tento stĺpec. Toto
- binárne nepotrebujem to odstráni
- ten stĺpec. Na páse s nástrojmi kliknite na položku Zavrieť a načítať.
- Za pár sekúnd budete mať prehľad o
- všetko v tomto priečinku, ak je priečinok
- zmeny, ktoré sem môžem vstúpiť a môžem
- obnovte dopyt a vráti sa späť
- a vytiahnite tieto údaje správne
- pre mňa je to problém, na ktorý sme zvyknutí
- mali by sme stále rozposielať 200
- rozpočtové súbory
- a niekoho dostanete späť, nie všetky
- späť musíte byť schopní porovnávať
- teraz môžem v podstate urobiť vlookup
- medzi priečinkami.
- Je úžasné, ako
- super to je, ale pozri, poďme ďalej
- 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.
- Táto kniha bude učiť
- všetko o dotaze na napájanie
- rozhranie je to uzasna kniha najlepsie
- kniha o moci dotaz všetko, čo som sa naučil
- Poučil som sa z tejto knihy. Nastúpil som na let z
- Orlando do Dallasu - prečítal som si celú knihu
- a moje znalosti dotazu na moc
- vyskočil za dve hodiny môžete byť až
- rýchlosť a nahradiť veci, ktoré by ste chceli
- mali zvyknutí robiť s VBA.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2037.xlsx
