Волшебный трюк Excel 1357: функция Power Query для повторения шагов очистки и преобразования для ...

Скачать файлы:

Начальный файл: https://excelisfun.net/files/EMT1357S...

https://excelisfun.net/files/1357Sour...

Конечный файл: https://excelisfun.net/files/EMT1357F...

Скачать файл: http://people.highline.edu/mgirvin/ex...
Цель: Отменить сведение и очистить кросс-табличные таблицы в нескольких файлах Excel и создать единый набор данных. Создайте функцию Power Query для многократного выполнения задачи очистки и преобразования в нескольких файлах Excel. Узнайте, как использовать новую функцию «Вызвать пользовательскую функцию» для пользовательского столбца. Изучите множество мощных функций Power Query (получение и преобразование):
1. (00:16) Введение: обзор всего процесса, включая просмотр файлов, которые необходимо импортировать, очистить, преобразовать и консолидировать.
2. (02:00) Создание пользовательской функции Power Query с помощью перечисленных ниже шагов (3–12).
3. (03:47) Транспонирование таблицы (чтобы учесть тот факт, что есть два заголовка столбцов с условиями, которые необходимо отменить свёртывание).
4. (04:30) Повышение заголовков.
5. (04:48) Отмена свёртывания других столбцов на основе двух столбцов «Дата» и «Справедливая».
6. (05:40) Переименование столбцов.
7. (06:05) Закрытие и загрузка с сохранением «Только создание подключения».
8. (06:34) Дублирование запроса.
9. (06:52) Изучение M-кода и того, как он настраивается и автоматически пишется при использовании пользовательского интерфейса Power Query.
10. (08:27) Добавьте строки M-кода для преобразования дублирующегося запроса в пользовательскую функцию Power Query.
11. (09:42) Импорт файлов из папки: импорт нескольких файлов Excel с кросс-таблицами, которые необходимо очистить и отменить свёртывание.
12. (10:34) Преобразуйте расширение столбца в строчные буквы, а затем отфильтруйте только файлы Excel с расширением «.xlsx».
13. (11:25) Добавьте настраиваемый столбец с функцией Excel.Workbook для получения объектов Excel, таких как листы.
14. (12:58) Разверните настраиваемый столбец для отображения объектов и для отображения листов с кросс-таблицами.
15. (13:37) Фильтр для импорта только объектов листа.
16. (14:09) Отфильтровать вкладки листов, содержащие слово «Лист» (вкладки листов, на которых нет имени торгового представителя).
17. (14:30) Удалить другие столбцы (не данные и не имя)
18. (14:42) Вызвать пользовательскую функцию Power Query
19. (14:34) Удалить столбец данных
20. (14:39) Развернуть столбцы
21. (15:55) Задать типы данных и переименовать столбцы
22. (16:34) Загрузить на лист Excel (таблица, только создать подключение или модель данных)
23. (17:36) Протестировать обновление, перетащив новые файлы в нашу папку
24. (18:20) Резюме