[БЕЗ_ЗВУКА] Блок «Очистка данных в Power Query». Для любой задачи по анализу данных требуются эти самые данные. В идеальной ситуации вся информация хранится структурированно в специальных хранилищах, которые называют «базы данных», а отвечают за порядок в этой информации специальные люди — администраторы баз данных. Они следят за тем, чтобы в информации не было дублей, пропусков, каких-то неверных значений и других неприятностей. В реальной же жизни зачастую нам приходится работать с неструктурированными источниками информации. Другими словами, попросту, файлами Excel, которые создают люди или даже несколько человек. Такие файлы могут в себе содержать целый букет подводных камней, начиная от самых простых опечаток и заканчивая дополнительными строками, шапками таблиц или объединёнными ячейками. Всё потому, что человеку так удобнее работать с информацией на компьютере — человеку, но не компьютеру. В тех ситуациях, когда вы хотите использовать инструменты по анализу и обработке данных, в частности, Power BI, вам необходимо загружать информацию в таком виде, как удобно компьютеру — таковы правила игры. В данном блоке мы освоим базовые приёмы очистки и подготовки данных с помощью Power Query. В частности, узнаем, как можно удалить лишние пробелы в тексте, как удалить дубликаты строк и исправить другие часто встречающиеся проблемы. Сейчас на экране открыт новый отчёт Power BI. Для того чтобы начать изучать различные приёмы очистки и обработки данных в Power Query, нам необходимо подключиться к файлу Excel. Для этого я нажимаю соответствующую кнопку, выбираю файл, и в появившемся окне «Навигатор» указываю необходимый мне лист, и нажимаю кнопку «Преобразовать данные», после чего запускается окно редактора Power Query. Наше знакомство с очисткой и обработкой информации в Power Query мы начнём с фильтрации данных. Фильтрация таблицы в Power Query выполняется очень легко и интуитивно понятно, очень похоже на работу в Excel. Всё, что нам нужно сделать — найти нужную колонку, допустим, «Страна», кликнуть на раскрывающийся список и в нём установить галочку напротив нужного значения. Нажать кнопку OK — и всё, фильтр применён! Теперь в моей таблице остались только те строки, где страна равна Франции. Но это не все возможности фильтрации в Power Query. Например, мы можем задавать более сложные фильтры на основе правил. Для этого в выпадающем списке необходимо выбрать опцию «Текстовые фильтры» и далее выбрать один из подходящих вариантов. Например, я хочу отфильтровать только те строки, где значения содержат английскую букву a. Для этого в появившемся окне я выбираю опцию «Содержит» и указываю нужную мне букву. Нажимаю кнопку OK, и теперь значение отфильтровано ровно так, как мне было нужно. Кроме этого, мы можем применять фильтры не к одной колонке, а сразу к нескольким. Например, я теперь вдобавок могу отфильтровать колонку «Сегмент» — для этого я открою выпадающий список и выберу здесь нужный мне сегмент. Вот так легко и просто с помощью нескольких кликов мы отфильтровали значения в нашей таблице на основе нескольких колонок. Кроме этого, мы также можем устанавливать правила фильтрации и на основе числовых колонок. В первую очередь это можно сделать привычным способом, просто указав нужные значения с помощью галочек. Но помимо этого, также можно указывать и более сложные правила. Например, я хочу отфильтровать те значения, которые меньше либо равны 240 условных единиц. Для этого в выпадающем списке мне нужно выбрать опцию «Числовые фильтры» и выбрать вариант «меньше или равно». В появившемся окне я указываю значение 240, нажимаю кнопку OK, после чего я вижу результат своего запроса. Далее перейдём к следующему пункту, а именно: как отфильтровать те строки, в которых есть пустые значения. Для этого я удалю последние шаги, которые я создал в своём запросе. Всё начинается там же, в выпадающем меню фильтрации. Для того чтобы отфильтровать пустые строки, есть отдельная специальная кнопка, которая называется «Удалить пустые». Она позволит вам отфильтровать те строки, где значение равно пустоте. Эта кнопка необходима, так как пустые значения могут быть в разных форматах, и именно она помогает позаботиться о всех из них. Далее мы перейдём к тому, каким образом можно регулировать количество колонок в вашей таблице. Предположим, что некоторые из колонок мне не нужны в моём будущем отчёте, и я хочу их удалить. Например, колонка Manufacturing Price. Всё, что мне нужно сделать — просто кликнуть правой кнопкой мыши и выбрать опцию «Удалить», после чего колонка исчезла из таблиц. Иногда встречаются ситуации, когда необходимо удалить не одну колонку, а сразу несколько. Или ещё сложнее ситуация, когда нужно удалить все колонки, кроме выбранных. На этот случай есть специальная функция в Power Query, которой можно воспользоваться следующим образом. Нам необходимо выбрать те колонки, которые мы хотим оставить, например, вот эти, далее кликнуть правой кнопкой мыши и выбрать опцию «Удалить другие столбцы», после чего все прочие столбцы будут удалены. Данная функция бывает очень полезна, особенно в тех ситуациях, когда вы работаете с файлами Excel, сформированными вручную, поскольку файлы Excel — это такая живая субстанция, которая постоянно изменяется, редактируется и дополняется, и вполне вероятно, что после того, как вы создадите ваш запрос, через какое-то время в файле появятся новые колонки, и если вы удалите какие-то конкретно ненужные вам колонки, то новые колонки удаляться не будут и они загрузятся в ваш отчёт, что может привести к различным последствиям. Для того чтобы уберечь себя от таких ситуаций, следует воспользоваться вышеуказанной функцией. Далее мы перейдём к тому, как можно управлять количеством строк в нашей таблице. Для этого я отменю последнее действие, и нам необходимо будет воспользоваться кнопками на панели кнопок в верхней части экрана на вкладке «Главная». В частности, нас будут интересовать кнопки в группе «Сократить строки». Здесь их две, «Сохранить строки» и «Удалить строки». Рассмотрим первую из них. В кнопке «Сохранить строки» у нас есть возможность сохранить какое-то количество верхних строк. Давайте для примера попробуем сохранить первые пять строк. Я ввожу цифру 5, нажимаю кнопку OK, и результат не заставил себя долго ждать. Аналогичным образом работает и кнопка «Сохранить нижние строки». Помимо этого, у нас есть возможность сохранить какой-то диапазон строк и также сохранить строки-дубликаты или те строки, в которых содержатся ошибки. Аналогичные же функции у нас располагаются и в кнопке «Удалить строки» — мы так же можем удалить определённое количество верхних, нижних или диапазон строк. Кроме этого, мы можем удалить строки-дубликаты, удалить пустые строки и удалить ошибки. Сейчас мы подробнее остановимся на кнопке «Удалить пустые строки». Данная функция позволяет удалять те строки, которые не содержат ни единого значения ни в одной колонке. Функция «Удалить ошибки» позволяет удалять те строки, в которых содержится хоть одна ошибка хоть в одной колонке. Таким образом, с использованием данной функции нужно быть предельно осторожным, поскольку если хотя бы в одной колонке закрадётся ошибка, то строка будет удалена целиком. О том, какие ошибки возможны в значениях таблицы, мы разберём немного позднее. Далее мы рассмотрим возможность замены значений в таблице. Для этого, предположим, в строке «Страна» мне необходимо заменить значение United States of America на сокращённое название USA. Всё, что мне нужно сделать — это выбрать необходимую колонку и найти кнопку «Замена значений» на ленте кнопок. Или же можно кликнуть правой кнопкой мыши и найти здесь нужную функцию. Разницы абсолютно нет никакой, результат будет одинаков. Итак, я выбираю функцию замены и ввожу значение. [БЕЗ_ЗВУКА] Нажимаю кнопку OK, и мы видим, что в колонке «Страна» теперь все значения United States of America заменены на сокращения USA. Далее мы рассмотрим возможности обработки текстовых значений с помощью Power Query. Для этого я отменю последний шаг и кликну правой кнопкой мыши на любой колонке с текстовыми значениями. Здесь меня интересует функция «Преобразование». Здесь мы наблюдаем несколько различных опций. Что же делает данная функция? Она обрабатывает каждое значение в колонке и изменяет его. Например, функция преобразования «Нижний регистр» сделает так, что все буквы в тексте станут прописными, «Верхний регистр» — наоборот, сделает все буквы заглавными. Как вы понимаете, функция «Каждое слово с прописной буквы» сделает каждую первую букву слова заглавной. К сожалению, здесь допущена опечатка при переводе функций с английского языка на русский. Надеюсь, что компания Microsoft в ближайшее время её найдёт и исправит. Особое внимание я хотел бы уделить функции «Усечь». Данная функция позволяет очистить лишние пробелы в начале и в конце строки. То есть если у вас в значении написано слово «Дмитрий» и в конце стоит пробел, то функция «Усечь» удалит один символ — удалит последний пробел, и останется только слово «Дмитрий». При этом прошу обратить внимание, если в значении написано «Добрый день» через пробел, то пробел между словами функция «Усечь» оставит без изменений. Функция «Очистить» позволяет удалять все непечатные символы в строке. Это различные технические символы, такие как перенос строки и прочее. Они, как правило, не видны, но порой встречаются при выгрузке данных из различных систем. Функция «Длина» позволяет вернуть длину строки каждого значения, а функции JSON и XML нам не потребуются на первых порах изучения Power Query. Далее мы перейдём к тому, какие ошибки в таблицах данных могут встречаться при работе с Power Query. Для этого мне необходимо открыть мой файл Excel и намеренно внести туда ошибку. Я введу самую распространённую формулу, которая зачастую приводит к ошибкам. Это функция ВПР, она очень часто может приводить к ошибкам в тех ситуациях, когда значение не было найдено, либо был изменён диапазон, либо по каким-то иным причинам. Итак, я ввёл функцию ВПР, и так как я специально ввёл её некорректной, то она возвращает значение НД (недействительно). Теперь я сохраню свой файл Excel и вернусь в программу Power BI Desktop, а точнее, в окно Power Query, и для того чтобы увидеть изменения, которые произошли в файле, мне необходимо кликнуть кнопку «Обновить предпросмотр». После этого, пожалуйста, обратите внимание, в третьей строке, в колонке Product появилось значение Error. Для того чтобы просмотреть текст ошибки, необходимо кликнуть в ячейке рядом с данной ошибкой, чтобы ячейка была активна, и обратить внимание на нижнюю часть экрана — вот здесь, в окне просмотра значения, будет указан текст ошибки. Здесь мы видим текст «Недопустимое значение ячейки #N/A», то есть «#Н/Д» в русскоязычной версии Excel. Итак, чем же грозит нам эта ошибка? На самом деле всё не так страшно. Если загрузить подобную таблицу в Power BI, то значение с ошибкой будет автоматически заменено на пустоту, и отчёт продолжит работать. Но в случае, если вы захотите в рамках Power Query ещё каким-либо образом взаимодействовать с этой колонкой, а точнее, с этой строкой, а точнее, с этой конкретной ячейкой, то это может привести к ошибкам. Например, если мы теперь попробуем отфильтровать, то обратите, пожалуйста, внимание: последняя строка номер три полностью заполнена ошибками, то есть Power Query начинает отрабатывать подобные ситуации с ошибкой. Именно поэтому для стабильной работы Power Query необходимо обрабатывать возможные ошибки в ваших данных. О том, как их обрабатывать, мы сейчас поговорим. Прежде этого, я хотел бы обратить ваше внимание на строку состояния в каждой колонке. Если мы посмотрим на колонку Country, то здесь, под названием колонки, мы увидим зелёную строку. Она сигнализирует нам о том, что со значениями в данной колонке всё в порядке. Если же мы обратим внимание на колонку Product, то здесь мы видим, что строка в самом начале красная — это означает, что в значениях колонки возможны ошибки, и это действительно так. Таким образом, подобные строки состояния у каждой колонки помогают вам определить, всё ли в порядке с вашими данными или требуется дополнительное внимание. Итак, давайте поговорим. Что можно сделать с ошибкой? Первое, что нам доступно — это просто заменить ошибку на какое-либо значение. Для этого необходимо кликнуть на колонке, выбрать правой кнопкой мыши меню и здесь найти функцию «Заменить ошибки». В появившемся окне можно ввести какое-либо значение, если это требуется, или же просто-напросто оставить окно пустым, и тогда после нажатия кнопки OK наша ошибка будет заменена на пустоту. Это самый приемлемый вариант, который позволяет обрабатывать ошибки. Следующий вариант обработки ошибок — это возможность удалить строку целиком. Для этого я отменяю последнее своё действие, чтобы ошибка вернулась на место, и далее, если мы кликнем по кнопке «Удалить строки», «Удалить ошибки», то вся строка номер три целиком будет удалена. Данной функцией стоит пользоваться с осторожностью, поскольку это может привести к потере данных в вашей таблице. Также стоит отметить, что в случае, если у вас ошибки содержатся в нескольких колонках или вы подразумеваете, что они могут там быть, то вам необходимо выполнять замену ошибок для каждой колонки по отдельности. К сожалению, нельзя выделить несколько колонок сразу и заменить ошибки для них, поскольку здесь такой функции попросту нет. Поэтому вам придётся кликать правой кнопкой мыши на каждой колонке и выбирать функцию «Замена значений». В данном блоке мы узнали о приёмах очистки и подготовки данных с помощью Power Query. Эти навыки будут незаменимы при работе с неструктурированными источниками данных, когда требуется удалить пробелы, дубликаты, заменить ошибки или попросту отфильтровать строки.
