Power Query - это новый инструмент от Microsoft для извлечения, преобразования и загрузки данных. Сегодняшняя статья посвящена обработке всех файлов в папке.
Power Query встроен в Excel 2016 и доступен для бесплатной загрузки в некоторых версиях Excel 2010 и Excel 2013. Инструмент предназначен для извлечения, преобразования и загрузки данных в Excel из различных источников. Лучшая часть: Power Query запоминает ваши шаги и воспроизводит их, когда вы хотите обновить данные. В момент публикации этой книги функции Power Query в Excel 2016 находятся на вкладке «Данные» в группе «Получить и преобразовать» в разделе «Новый запрос». Трудно предсказать, будет ли Microsoft задним числом переименовывать Power Query в Get & Transform в Excel 2010 и Excel 2013.
Эта бесплатная надстройка настолько удивительна, что о ней можно было бы написать целую книгу. Но в качестве одного из 40 моих лучших советов я хочу охватить кое-что очень простое: вывести список файлов в Excel вместе с датой создания файла и, возможно, размером. Это полезно для создания списка бюджетных книг или списка фотографий.
В Excel 2016 вы выбираете Данные, Новый запрос, Из файла, Из папки. В более ранних версиях Excel используйте Power Query, из файла, из папки. Укажите папку:
При редактировании запроса щелкните правой кнопкой мыши любые столбцы, которые вам не нужны, и выберите «Удалить».
Чтобы получить размер файла, щелкните этот значок в столбце Атрибуты:
Появится список дополнительных атрибутов. Выберите размер.
Доступен большой список опций преобразования.
Когда вы закончите редактирование запроса, нажмите «Закрыть и загрузить».
Данные загружаются в Excel в виде таблицы.
Позже, чтобы обновить таблицу, выберите Данные, Обновить все. Excel запоминает все шаги и обновляет таблицу, добавляя текущий список файлов в папке.
Полное описание функции, ранее известной как Power Query, можно найти в статье M для (Data) Monkey, написанной Кеном Пулсом и Мигелем Эскобаром.
M означает (ДАННЫЕ) ОБЕЗЬЯНА »
Спасибо Мигелю Эскобару, Робу Гарсиа, Майку Гирвину, Рэю Хаузеру и Колину Майклу за выдвижение Power Query.
Смотреть видео
- Инструменты Power Query находятся на вкладке «Данные» в Excel 2016.
- Бесплатная надстройка на 2010 и 2013 годы
- Перечислить все файлы из папки в сетку Excel с помощью Power Query
- Выберите новый запрос, из файла, из папки
- Неочевидно: разверните поле атрибута, чтобы получить размер
- Если ваши данные находятся в файлах CSV, вы можете импортировать все файлы сразу в единую сетку.
- Продвигайте строку заголовка
- Удалите оставшиеся строки заголовка
- Заменить "" на ноль
- Заполните для просмотра контура
- Удалить столбец общей суммы
- Разверните данные
- Формула для преобразования названий месяцев в даты
- Полный список шагов - величайшая в мире отмена
- На следующий день - обновите запрос, чтобы повторить все шаги
Стенограмма видео
- Power Query встроен в версии Excel 2016 для Windows. Посмотрите на вкладку «Данные» в группе «Получить и преобразовать». Если у вас 2010 или
- 2013, пока вы используете Windows
- а не Mac все, что есть в Get & Transform
- вы можете бесплатно скачать с сайта Microsoft. Просто ищи
- Загрузите Power Query.
- Сегодня меня интересует использование Power Query для получения списка файлов. я
- хотите перечислить все файлы в папке.
- Может быть, мне нужно посмотреть, какие файлы
- большие файлы или мне нужно отсортировать или мне нужно
- вы знаете, чтобы получить комбинацию из вас
- знать файлы бюджета, которые мы разослали
- а затем другую папку, которая
- мы вернулись.
- Для начала выберите «Данные», «Получить и преобразовать», «Из файла», «Из папки».
- Вставьте путь к папке или используйте кнопку «Обзор».
- Нажмите ОК, и они покажут мне это
- предварительный просмотр. Выберите Edit.
- Вот пара вещей, которые вы видите, у нас есть
- имя файла расширение дата
- доступ, дата изменения, дата создания.
- На самом деле не очевидно, что этот символ рядом с заголовком «Атрибуты» означает «Развернуть». Щелкните этот символ, и в
- здесь, и если вы щелкните этот символ, то я
- может войти и получить такие вещи, как размер файла
- или если он доступен только для чтения и тому подобное
- что в данном случае я просто хочу файл
- размер. Выберите Размер файла. Щелкните ОК. Они предоставляют вам новое поле с именем 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.
- Эта книга научит
- Вы все о запросе мощности
- интерфейс это потрясающая книга лучший
- книга по мощности запросить все, что я узнал
- Я узнал из этой книги. Я прилетел из
- Орландо в Даллас - я прочитал всю книгу
- и мои знания о power query просто
- взлетел за два часа, вы можете быть до
- ускорить и заменить то, что вы бы
- привыкли к VBA.
Скачать файл
Загрузите образец файла здесь: Podcast2037.xlsx
