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 від Кена Пулса та Мігеля Ескобара.
М для (ДАНІ) МАВПИ »
Подяка Мігелю Ескобару, Робу Гарсії, Майку Гірвіну, Рей Хаузеру та Коліну Майклу за номінацію Power Query.
Переглянути відео
- Інструменти Power Query знаходяться на вкладці Дані в Excel 2016
- Безкоштовна надбудова для 2010 та 2013 років
- За допомогою Power Query перелічіть усі файли з папки в сітку Excel
- Виберіть Новий запит, З файлу, З папки
- Не очевидно: розгорніть поле атрибута, щоб отримати розмір
- Якщо ваші дані містяться у файлах CSV, ви можете імпортувати всі файли відразу в одну сітку
- Просуньте рядок заголовка
- Видаліть решту заголовків
- Замініть "" на null
- Заповніть для подання контуру
- Видаліть колонку загальної суми
- Видаліть дані
- Формула для перетворення назв місяців у дати
- Повний перелік кроків - найбільше у світі скасування
- Наступного дня - оновіть запит, щоб повторити всі кроки
Стенограма відео
- Power Query вбудований у версії Windows Excel 2016. Подивіться на вкладку Дані в групі Отримати та перетворити. Якщо у вас 2010 або
- 2013 рік, поки у вас Windows
- а не Mac, все, що тут є в Get & Transform
- Ви можете завантажити безкоштовно від Microsoft. Просто шукайте
- Завантажте Power Query.
- Сьогодні я зацікавлений у використанні Power Query для отримання списку файлів. Я
- хочу перерахувати всі файли в папці.
- Можливо, мені потрібно побачити, які файли є
- великі файли, або мені потрібно сортувати, або мені потрібно
- Ви знаєте, щоб отримати поєднання вас
- знати бюджетні файли, які ми розіслали
- а потім іншу папку, які саме
- ми прийшли повернулися.
- Для початку перейдіть до Дані, Отримати та трансформувати, З файлу, З папки.
- Вставте шлях до папки або скористайтеся кнопкою Огляд.
- Натисніть ОК, і вони мені це покажуть
- попередній перегляд. Виберіть Редагувати.
- Ви бачите, що тут є кілька речей
- назва файлу розширення дата
- доступ, дата зміни, дата створення.
- Насправді не очевидно, що цей символ поруч із заголовком Атрибути означає Розгорнути. Клацніть на цей символ, і там буде більше речей
- тут і якщо натиснути цей символ, то я
- можна зайти і отримати такі речі, як розмір файлу
- або якщо він доступний лише для читання і тому подібне
- так що в цьому випадку я просто хочу файл
- розмір. Виберіть Розмір файлу. Клацніть добре. Вони дадуть вам нове поле з назвою 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
