Power Query е нов инструмент от Microsoft за извличане, трансформиране и зареждане на данни. Днешната статия е за обработка на всички файлове в папка.
Power Query е вграден в Excel 2016 и се предлага като безплатно изтегляне в определени версии на Excel 2010 и Excel 2013. Инструментът е проектиран да извлича, трансформира и зарежда данни в Excel от различни източници. Най-добрата част: Power Query помни стъпките ви и ще ги възпроизведе, когато искате да опресните данните. Тъй като тази книга ще излезе, функциите на Power Query в Excel 2016 са в раздела Данни, в групата Получаване и трансформиране под Нова заявка. Трудно е да се предскаже дали Microsoft ще преименува с обратна сила Power Query за получаване и преобразуване в Excel 2010 и Excel 2013.
Тази безплатна добавка е толкова невероятна, че може да има цяла книга за нея. Но като един от моите 40 най-добри съвета, искам да разгледам нещо много просто: въвеждане на списък с файлове в Excel, заедно с датата на създаване на файла и може би размера. Това е полезно за създаване на списък с бюджетни работни книги или списък със снимки.
В Excel 2016 избирате Данни, Нова заявка, От файл, От папка. В по-ранните версии на Excel използвайте Power Query, From File, From Folder. Посочете папката:
Докато редактирате заявката, щракнете с десния бутон върху всички колони, които не искате, и изберете Премахване.
За да получите размера на файла, щракнете върху тази икона в колоната Атрибути:
Появява се списък с допълнителни атрибути. Изберете Размер.
Наличен е голям списък с опции за преобразуване.
след като сте готови с редактирането на заявката, щракнете върху Затваряне и зареждане.
Данните се зареждат в Excel като таблица.
По-късно, за да актуализирате таблицата, изберете Данни, Обнови всички. Excel запомня всички стъпки и актуализира таблицата с текущ списък с файлове в папката.
За пълно описание на функцията, известна преди като Power Query, разгледайте M is for (Data) Monkey от Ken Puls и Miguel Escobar.
М е за (ДАННИ) МАЙМУНА »
Благодаря на Мигел Ескобар, Роб Гарсия, Майк Гирвин, Рей Хаузър и Колин Майкъл за номинирането на Power Query.
Гледам видео
- Инструментите на Power Query са в раздела Данни в Excel 2016
- Безплатна добавка за 2010 и 2013 г.
- Избройте всички файлове от папка в мрежата на Excel с помощта на Power Query
- Изберете Нова заявка, От файл, От папка
- Не е очевидно: разширете полето на атрибута, за да получите размер
- Ако данните ви са в CSV файлове, можете да импортирате всички файлове наведнъж в една мрежа
- Повишете заглавния ред
- Изтрийте останалите заглавни редове
- Заменете "" с null
- Попълнете за контурния изглед
- Изтрийте общата колона
- Депилирайте данните
- Формула за преобразуване на имената на месеците в дати
- Пълен списък със стъпки - най-голямото отмяна в света
- На следващия ден - опреснете заявката, за да повторите всички стъпки
Стенограма на видеото
- Power Query е вграден в Windows версии на Excel 2016. Погледнете раздела Данни в групата Получаване и трансформиране. Ако имате 2010 или
- 2013, стига да използвате Windows
- а не Mac всичко, което е тук в Get & Transform
- можете да изтеглите безплатно от Microsoft. Просто потърсете
- Изтеглете Power Query.
- Днес ми е интересно да използвам Power Query, за да получа списък с файлове. Аз
- искате да изброите всички файлове в папка.
- Може би трябва да видя кои файлове са
- големи файлове или трябва да сортирам или имам нужда
- знаете, за да получите комбинация от вас
- знаем бюджетните файлове, които изпратихме
- и след това различна папка кои
- дойдохме се върнахме.
- За да започнете, отидете на Data, Get & Tranform, From File, From Folder.
- Поставете в пътя на папката или използвайте бутона Преглед.
- Щракнете върху OK и те ми показват това
- визуализация. Изберете Редактиране.
- Няколко неща тук виждате, че имаме
- името на файла разширението датата
- достъп, дата на изменение, дата на създаване.
- Наистина не е очевидно, че този символ до заглавието Атрибути означава Разширяване. Щракнете върху този символ и има още неща
- тук и ако щракнете върху този символ, тогава аз
- можете да влезете и да получите неща като размера на файла
- или ако е само за четене и подобни неща
- така че в този случай просто искам файл
- размер. Изберете Размер на файла. Щракнете добре Те ви дават ново поле с име Attributes.Size.
- Виждам колко байта са в
- всеки файл.
- Може би нямам нужда от всичко тук, може би
- Нямам нужда от създадената дата, за да мога
- щракнете с десния бутон и кажете, че искам
- премахнете тази колона. Това
- двоичен не се нуждая, че ще премахне
- тази колона. От лентата щракнете върху Затваряне и зареждане.
- След няколко секунди ще имате сортируем изглед на
- всичко в тази папка, ако папката
- промени мога да вляза тук и мога
- опреснете заявката и тя ще се върне
- и издърпайте данните вдясно, това е
- за мен това е проблем, който използвахме преди
- имаме през цялото време, бихме изпратили 200
- бюджетни файлове
- и ще получите някой обратно не всички
- обратно трябва да можете да сравните така
- сега по същество мога да направя vlookup
- между папките.
- Просто е невероятно как
- готино е, но вижте да отидем отвъд
- 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.
- Тази книга ще научи
- вие всичко за заявката за захранване
- интерфейс това е невероятна книга най-доброто
- книга за мощност заявка всичко, което научих
- Научих от тази книга. Качих се на полет от
- Орландо до Далас - прочетох цялата книга
- и моите познания за енергийна заявка просто
- скочи за два часа, можете да бъдете до
- ускорете и заменете нещата, които бихте направили
- някога сте правили с VBA.
Свали файл
Изтеглете примерния файл тук: Podcast2037.xlsx
