Тема разбита на несколько статей, чтобы чтение для вас было более комфортным. Серия публикаций познакомит с основными операциями в редакторе запросов Power Query.
Для практики решим прикладную задачу: преобразуем выгруженную из 1С Оборотно-сальдовую ведомость, в "плоскую" таблицу для построения Сводной таблицы.
Видно, что таблица примера, содержит многоуровневые заголовки, пустые строки, группировку:
Это не позволяет нам манипулировать с данными без их предварительного преобразования.
Следуя по шагам, мы сначала преобразуем данные в удобную для работы "плоскую" таблицу:
На ее основании построим Сводную таблицу:
Исходная таблица выбрана для примера, однако изучив основные операции в редакторе Power Query вы сможете производить манипуляции с любыми данными.
Наметим для себя план преобразования:
- Загрузка файла EXCEL в редактор;
- Отмена действия в редакторе;
- Удаление пустых строк;
- Удаление пустых столбцов;
- Создание дубликата столбца;
- Разделение столбца ", ";
- Транспонирование столбцов;
- Заполнение Вниз;
- Объединение столбцов;
- Обратное Транспонирование;
- Преобразование строки в заголовки столбцов;
- Отмена свертывания столбцов (Unpivot);
- Разделение столбца по разделителю "_";
- Переименование заголовков таблицы;
- Изменение типа данных;
- Удаление лишних данных фильтром;
- Выгрузка данных в Сводную таблицу.
В редакторе действия выглядят так:
Отмена действия
При подключении данных Power Query пытается автоматически определить тип загруженных данных, скажем мягко не всегда у него это получается. Удалить этот и последующие шаги в редакторе при помощи Ctrl+Z, как в Excel не получится.
Что же делать? Все просто, шаги записываются и отображаются в окне Параметры запроса ► Примененные шаги:
Нажмите крестик слева от действия или нажав правую кнопку мыши (пкм) в меню ► Удалить.
Удаление строк
Определитесь сколько строк и откуда (сверху или снизу) вам нужно удалить, в примере нужно удалить семь строк сверху и пять снизу;
Выберите команду на ленте Главная ► (Сократить строки) Удалить строки ► Удаление верхних строк:
Введите количество строк для удаления и нажмите Оk:
Повторите для нижних строк Главная ► (Сократить строки) Удалить строки ► Удаление нижних строк.
Готово.
Если, нужно удалить все пустые строки, на вкладке Главная, в группе Сократить строки, нажмите кнопку Удалить строки и выберите Удалить пустые строки:
Удаление столбцов
Бывает, что вы получаете данные, и вам нужно удалить некоторые столбцы, которые для вашего отчета не нужны. В Power Query вы можете создать запрос, при этом данные в исходной таблице останутся неизменными:
Выберите столбец или несколько столбцов (зажав клавишу Shift или Ctrl) и нажмите пкм ► Удалить столбцы:
или выберите команду на ленте в разделе Управление столбцами Удалить столбцы.
Опция Удалить другие столбцы, удаляет все не выбранные столбцы.
Второй способ удаления пустых столбцов, с использованием Транспонирования:
- На вкладке Преобразование в группе Таблица выбрать Транспонировать;
- Затем на вкладке Главная страница ► Удалить строки ►Удалить пустые строки;
- Затем транспонируйте таблицу обратно — на вкладке Преобразование ► Таблица ► Транспонировать.
При использовании последнего способа, убедитесь, что ваши данные не содержат лишних строк в конце списка, которые могут вызвать не полное удаление пустых строк, например подписи бланка документа.
Спасибо, что дочитали до конца!
Ставьте лайк, если интересно, пишите, что думаете в комментариях и подписывайтесь на канал, если еще не сделали этого.
Интересное по теме:
- Power Query: мощь и простота работы с данными в Excel
