Валентин Арьков - Бизнес-аналитика. Извлечение, преобразование и загрузка данных

Power Query Editor.

Рассмотрим полученную таблицу.

Первая строка использована для формирования заголовков.

Даты представлены как целые числа.

Последние 5 столбцов — числовые значения.

Рис. 5.9. Редактор запросов

Задание. Загрузите данные и изучите форму запроса.

Теперь можно заняться очисткой и преобразованием данных.

Попробуем удалить лишние столбцы:

— Source_Name;

— PER;

— TIME.

Щёлкаем правой кнопкой по первому столбцу и выбираем в контекстном меню:

Remove.

Удалим два других лишних столбца.

Нажимаем кнопку Закрыть и загрузить:

Close & Load.

Рис. 5.10. Удаление столбца

Задание. Удалите лишние столбцы.

Наши файлы имеют однотипный формат. В первой строке файла расположены названия полей (столбцов).

Рассмотрим объединённую таблицу (рис. 5.11).

На границе данных по Газпрому и Сбербанку лишних строк не наблюдается.

Будем считать, что заголовки обработаны корректно. Теперь у нас один общий заголовок.

Рис. 5.11. Результаты загрузки

Задание. Рассмотрите данные в полученной таблице и убедитесь в отсутствии лишних строк (второго заголовка).

Возвращаемся к редактору запросов.

Щёлкаем правой кнопкой по нашему запросу и выбираем в контекстном меню редактирование:

Edit.

Проверим, что у нас есть в колонке тикеров (рис. 5.12).

Нажимаем кнопку фильтрации в заголовке первого столбца.

Список значений содержит всего два варианта:

— GAZP

— SBER

Мы ещё раз убедились, что первая строка второго файла была удалена, и у нас не появился второй заголовок. Надеемся, что загрузка следующих файлов пройдёт точно так же.

Рис. 5.12. Проверка лишних строк

Задание. Вызовите меню фильтрации и изучите значения в ячейках первого столбца.

Поработаем со столбцом дат.

Здесь четыре цифры года, две цифры месяца и две цифры числа.

Щёлкнем по столбцу дат и попробуем выбрать тип значений — Дата:

Home — Transform — Data Type — Date.

Попытка не удалась. Во всех ячейках столбца выводится сообщение об ошибке (рис. 5.13).

Нажимаем красный крестик в списке применённых шагов и откатываем последнее преобразование.

Рис. 5.13. Ошибка преобразования типа «Дата»

Задание. Измените тип данных на «Дату». При появлении сообщений об ошибке откатите изменения.

Подойдём к преобразованию даты с другой стороны.

Выделим год, месяц и день в отдельные колонки.

Щёлкаем по столбцу дат.

Нажимаем в верхнем меню кнопку разделения столбца:

Home — Transform — Split Column — By Number of Characters.

Всплывающая подсказка поясняет, что мы будем работать с фрагментами строки заданной длины.

Рис. 5.14. Разделение столбца

Появляется диалоговое окно для настройки разделения окна:

Split Column by Number of Characters.

Указываем количество символов (рис. 5.15):

Number of characters — 4.

Задание. Разделите столбец на части по 4 символа.

Рассмотрим результаты (рис. 5.16).

Теперь у нас два столбца вместо одного.

В обоих столбцах тип данных — целые числа.

Номер года выглядит хорошо. А вот номер месяца и дня пострадал. Вместо 0306 получилось 306. Ноль перед числом удалили, и осталось три цифры. Если нам попадутся месяцы 10, 11 или 12, то в ячейке будет четыре цифры. И с таким содержимым работать будет совсем тяжело.

Рис. 5.16. Результаты разделения столбца

Задание. Изучите результаты разделения столбца.

Обратим внимание, что в списке применённых шагов есть два действия:

— Split Column by Position — разделение столбцов по количеству символов;

— Changed Type1 — изменение типа значения в ячейках.

Откатим изменение типа и посмотрим на результат (рис. 5.17).

Обе колонки стали текстовыми, и обе содержат по четыре символа.

Рис. 5.17. Тип значений — текст

Задание. Отмените преобразование типа и обратите внимание на тип данных в столбцах.

Разделим второй столбец на два — чтобы в каждом было по два символа. И снова отменим преобразование типа.

Теперь у нас три столбца, и все три — текстовые (рис. 5.18).

Рис. 5.18. Текстовый тип значений

Задание. Разделите столбец на месяц и день и отмените преобразование типа.

Объединим наши три столбца, но поставим между значения разделитель — точку.

Выделяем три столбца, нажав клавишу Ctrlи щёлкая по столбцам.

Вызываем объединение столбцов:

Transform — Text Column — Merge Columns.

Появляется диалоговое окно

Merge Columns.

Настроим параметры объединения столбцов (рис. 5.19).

Разделитель — пользовательская настройка — точка:

Separator — Custom — (.).

Название объединённого столбца — Дата:

New column name — Date.

Нажимаем ОК.

Рис. 5.19. Настройка объединения столбцов

Задание. Объедините столбцы с использованием разделителя.

Посмотрим, что у нас получилось (рис. 5.20).

Четыре цифры — год. Точка. Две цифры — месяц. Точка. Две цифры — день.

Тип данных — текст.

Рис. 5.20. Объединённый столбец

Задание. Рассмотрите результаты объединения столбцов.

После преобразования типа наш столбец стал похож на дату (рис. 5.21). Правда, это дата в американском формате:

месяц/день/год.

В заголовке столбца указано, что это дата — видно значок календаря.

В группе Transformтоже говорится, что это дата:

Data Type — Date.

Рис. 5.21. Столбец «Дата» в формате даты

Задание. Преобразуйте тип данных в дату.

Теперь для полноты картины подправим заголовки остальных столбцов. Щёлкаем по заголовку и выбираем в контекстном меню пункт Rename. Редактируем каждый заголовок и убираем угловые скобки (рис. 5.22).

Рис. 5.22. Отредактированные заголовки

Задание. Отредактируйте заголовки и обратите внимание на изменения в списке применённых шагов.

Мы настроили преобразование данных.

Нажимаем ОКи получаем объединённую таблицу (рис. 5.23).

Заголовки исправлены.

Числа не пострадали.

Но дата пока что выводится в американском формате.

Рис. 5.23. Объединённая таблица.

Нам остаётся настроить формат даты.

Выделяем диапазон ячеек с датами и настраиваем формат вывода даты (рис. 5.24).

Рис. 5.24. Формат даты

Задание. Настройте формат даты.

Теперь добавим файлы в нашу папку.

Скачаем котировки ещё трёх крупнейших компаний из состава индекса:

— ROSN — Роснефть;

— LKOH — Лукойл;

— GMKN — Горно-металлургический комбинат (ГМК) Норильский никель.

Теперь в нашей папке пять файлов с котировками (рис. 5.25).

Рис. 5.25. Добавляем файлы с котировками

Задание. Добавьте три файла с котировками.

Мы добавили файлы в папку.

Теперь обновим запрос к данным.