Действие 1
Переходим на вкладку «Данные» и выбираем нужный путь:
Данные Получить данные Из файла Из книги
Power Query: очистка «кривой» выгрузки
Пошаговая инструкция по преобразованию грязных данных в таблицу, готовую к анализу
Скачать исходный файлОтвратительная таблица!
Блок 1
Загрузка данных
Действие 2
Выберите лист «Данные» и нажмите команду «Преобразовать данные».
Действие 3
В открывшемся редакторе Power Query в окне «Параметры запроса» удалите автоматически добавленные шаги. Оставьте «Источник» и «Навигация».
Блок 2
Подготовка структуры
Шаг 1
Идём на вкладку «Главная» и выполняем последовательность:
Главная Удалить строки Удаление верхних строк Укажите «1» ОК
Шаг 2
Выберите команду «Использовать первую строку в качестве заголовков».
Шаг 3
Выделите столбцы «Клиент / ИНН», «Город», «Категория» и «Товар».
Преобразование Заполнить Вниз
Шаг 4
Удалите полностью пустые строки.
Главная Удалить строки Удалить пустые строки
Блок 3
Очистка данных
Шаг 5
Выделите столбец «Клиент / ИНН».
Преобразование Формат Каждое слово с прописной
Шаг 6
Откройте фильтр столбца «Клиент / ИНН».
Текстовые фильтры Не начинается с… «Итого»
Шаг 7
Разделите столбец «Клиент / ИНН».
Разделить столбец По разделителю Пробел Самый правый разделитель
Шаг 8
Переименуйте получившиеся столбцы в «Клиент» и «ИНН».
Шаг 9
Проверьте NULL в столбце «Сумма продаж».
1Оставьте в фильтре только NULL.
2Проверьте строки: это должна быть полностью пустая или служебная строка, а не реальная операция без суммы.
3Если строка лишняя, снимите галочку с NULL, чтобы исключить её.
Шаг 10
Создайте новый столбец.
Добавление столбца Настраиваемый столбец
Number.FromText(Text.Select(Text.From([#"Кол-во"]), {"0".."9", ",", "-"}),"ru-RU"
)Вставьте формулу в окно настраиваемого столбца и нажмите ОК.
Шаг 11
Создайте второй настраиваемый столбец «Сумма». Вставьте скопированную формулу и замените [Кол-во] на [Сумма продаж].
Блок 4
Финальная настройка
Шаг 12
Выделите старые столбцы «Кол-во» и «Сумма продаж».
Удалить столбцы Переименовать новые столбцы при необходимости
Блок 5
Выгрузка результата
Выгрузка
Загрузите готовые данные обратно в Excel.
Главная Закрыть и загрузить в… Таблица
Результат
Плоская таблица, пригодная для анализа
При появлении новых данных
Вам нужно сделать только одно: нажать правой кнопкой мыши по итоговой таблице и выбрать «Обновить».
После кейса
Остались вопросы?
Почему нельзя было просто отфильтровать строки с «ИТОГО»?
Что происходит с объединёнными ячейками после загрузки в Power Query и откуда берутся null?
Как работает формула с Text.Select и что означает каждая её часть?
Какие ошибки могут возникнуть на разных этапах и как их исправить?
Посмотрите фрагмент урока
Открыть видео отдельноБесплатный прямой эфир
Продолжите знакомство с Power Excel
Запишитесь на бесплатный прямой эфир, который состоится 23 сентября в 19:00 по Power Excel — и после регистрации вы получите:
- Подробный видеоразбор этого кейса
- Базовые уроки по Power Excel: интерфейс, подключение к данным, виды подключений, основные команды и первые преобразования
Следующий шаг
А на самом эфире разберём ещё больше реальных задач, и я покажу, как Power-инструменты помогают автоматизировать работу с данными.
Записаться на эфирПереход на страницу регистрации