Power Query to nowe narzędzie firmy Microsoft do wyodrębniania, przekształcania i ładowania danych. Dzisiejszy artykuł dotyczy przetwarzania wszystkich plików w folderze.
Dodatek Power Query jest wbudowany w program Excel 2016 i jest dostępny do bezpłatnego pobrania w niektórych wersjach programu Excel 2010 i Excel 2013. Narzędzie zostało zaprojektowane do wyodrębniania, przekształcania i ładowania danych do programu Excel z różnych źródeł. Najlepsza część: dodatek Power Query zapamiętuje Twoje kroki i odtworzy je, gdy zechcesz odświeżyć dane. W momencie publikacji tej książki funkcje dodatku Power Query w programie Excel 2016 znajdują się na karcie Dane w grupie Pobieranie i przekształcanie w obszarze Nowe zapytanie. Trudno przewidzieć, czy firma Microsoft z mocą wsteczną zmieni nazwę dodatku Power Query na Pobierz i przekształć w programach Excel 2010 i Excel 2013.
Ten darmowy dodatek jest tak niesamowity, że może być o nim cała książka. Ale jako jedna z moich 40 najlepszych wskazówek, chcę omówić coś bardzo prostego: przeniesienie listy plików do Excela, wraz z datą utworzenia pliku i być może rozmiarem. Jest to przydatne do tworzenia listy skoroszytów budżetowych lub listy zdjęć.
W programie Excel 2016 wybierz opcję Dane, nowe zapytanie, z pliku, z folderu. We wcześniejszych wersjach programu Excel użyj dodatku Power Query, z pliku, z folderu. Określ folder:
Podczas edytowania zapytania kliknij prawym przyciskiem myszy dowolne kolumny, których nie chcesz, i wybierz opcję Usuń.
Aby uzyskać rozmiar pliku, kliknij tę ikonę w kolumnie Atrybuty:
Pojawi się lista dodatkowych atrybutów. Wybierz rozmiar.
Dostępna jest duża lista opcji przekształcania.
Po zakończeniu edycji zapytania kliknij Zamknij i wczytaj.
Dane są ładowane do programu Excel w postaci tabeli.
Później, aby zaktualizować tabelę, wybierz opcję Dane, Odśwież wszystko. Excel zapamiętuje wszystkie kroki i aktualizuje tabelę aktualną listą plików w folderze.
Aby uzyskać pełny opis funkcji znanej wcześniej jako Power Query, zobacz M jak (dane) Monkey autorstwa Kena Pulsa i Miguela Escobara.
M jak (DANE) MONKEY »
Podziękowania dla Miguela Escobara, Roba Garcii, Mike'a Girvina, Raya Hausera i Colina Michaela za nominację Power Query.
Obejrzyj wideo
- Narzędzia Power Query znajdują się na karcie Dane w programie Excel 2016
- Darmowy dodatek na lata 2010 i 2013
- Wyświetl wszystkie pliki z folderu w siatce programu Excel za pomocą dodatku Power Query
- Wybierz Nowe zapytanie, Z pliku, Z folderu
- Nieoczywiste: rozwiń pole atrybutu, aby uzyskać rozmiar
- Jeśli Twoje dane są w plikach CSV, możesz zaimportować wszystkie pliki naraz do jednej siatki
- Promuj wiersz nagłówka
- Usuń pozostałe wiersze nagłówka
- Zastąp „” wartością null
- Wypełnij, aby wyświetlić konspekt
- Usuń kolumnę sumy całkowitej
- Cofnij przestawianie danych
- Formuła do konwersji nazw miesięcy na daty
- Pełna lista kroków - największe na świecie cofanie
- Następnego dnia - odśwież zapytanie, aby powtórzyć wszystkie kroki
Transkrypcja wideo
- Dodatek Power Query jest wbudowany w wersje programu Excel 2016 dla systemu Windows. Spójrz na kartę Dane w grupie Pobieranie i przekształcanie. Jeśli masz 2010 lub
- 2013, o ile używasz systemu Windows
- a nie wszystko na Maca w Get & Transform
- można pobrać bezpłatnie z witryny Microsoft. Po prostu wyszukaj
- Pobierz dodatek Power Query.
- Dziś jestem zainteresowany użyciem dodatku Power Query do uzyskania listy plików. ja
- chcesz wyświetlić wszystkie pliki w folderze.
- Może muszę sprawdzić, które pliki to
- duże pliki lub muszę posortować lub potrzebuję
- wiesz, aby uzyskać kombinację siebie
- znać pliki budżetowe, które wysłaliśmy
- a następnie inny folder, które
- wróciliśmy.
- Aby rozpocząć, przejdź do menu Dane, Pobierz & transformację, Z pliku, Z folderu.
- Wklej ścieżkę do folderu lub użyj przycisku Przeglądaj.
- Kliknij OK, a oni mi to pokazują
- zapowiedź. Wybierz Edytuj.
- Widzisz, że mamy kilka rzeczy
- nazwa pliku rozszerzenie data
- uzyskano dostęp, data modyfikacji, data utworzenia.
- Naprawdę nie jest oczywiste, że ten symbol obok nagłówka Atrybuty oznacza Rozwiń. Kliknij ten symbol i jest więcej rzeczy
- tutaj i jeśli klikniesz ten symbol, to ja
- może wejść i uzyskać takie rzeczy, jak rozmiar pliku
- lub jeśli jest tylko do odczytu i takie jak
- więc w tym przypadku chcę tylko plik
- rozmiar. Wybierz rozmiar pliku. Kliknij OK. Dają ci nowe pole o nazwie Attributes.Size.
- Widzę, ile jest bajtów
- każdy plik.
- Może nie potrzebuję tu wszystkiego
- Nie potrzebuję utworzonej daty, więc mogę
- kliknij prawym przyciskiem myszy i powiedz, że chcę
- usuń tę kolumnę. To
- binarny, którego nie potrzebuję, to usunie
- ta kolumna. Na Wstążce kliknij Zamknij i wczytaj.
- Za kilka sekund będziesz mieć uporządkowany widok
- wszystko w tym folderze, jeśli folder
- zmiany mogę tu wejść i mogę
- odśwież zapytanie i wróci
- wyciągnij i wyciągnij te dane
- dla mnie jest to problem, z którym mieliśmy do czynienia
- cały czas wysyłamy 200
- pliki budżetowe
- i odzyskasz kogoś, a nie wszystkich
- z powrotem musisz być w stanie porównać tak
- teraz mogę zasadniczo zrobić podgląd
- między folderami.
- To po prostu niesamowite
- fajnie jest, ale spójrzmy, wyjdźmy dalej
- 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.
- Ta książka nauczy
- wszystko na temat zapytania o moc
- interfejs to niesamowita książka najlepsza
- książka o mocy zapytaj o wszystko, czego się nauczyłem
- Nauczyłem się z tej książki. Dostałem się na samolot z
- Z Orlando do Dallas - przeczytałem całą książkę
- a moja wiedza na temat power query
- wzbił się w dwie godziny, możesz być do
- przyspieszyć i zastąpić rzeczy, które chcesz
- już kiedyś robiłem z VBA.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2037.xlsx
