Что такое Power Query
В прошлой статье мы писали о Power Pivot, одном из самых популярных надстроек для так называемой «самостоятельной бизнес-аналитики» в Excel. Power Pivot является краеугольным камнем моделирования данных в Excel, но есть и другие доступные надстройки, которые существенно помогают при создании отчетов в Excel. В этой статье мы более подробно рассмотрим надстройку Power Query (ранее называвшуюся Data Explorer), которая была представлена в конце 2013 года.
Мощный запрос
Power Query (далее просто PQ) в первую очередь предназначен для импорта данных в Excel. Вы можете прочитать, что это также возможно в Power Pivot, так зачем устанавливать еще одно дополнение. Поскольку PQ предназначен не только для импорта данных, благодаря ему у вас есть возможность редактировать данные, преобразовывать их и выбирать только те, которые вас интересуют. Это делает Power Query мощным инструментом ETL для импорта данных в модель данных (или на лист электронной таблицы) из различных источников.
По сравнению с Power Pivot количество внешних источников данных увеличилось, теперь можно легко загружать данные из базы данных MySQL/Postgre, Hadoop, Windows Azure HDInsight, SAP BI Universe, Facebook или с любого веб-сайта. Так, например, в википедии можно найти интересные данные, например, страницу о чемпионате мира по баскетболу, Power Query проанализирует всю страницу и если найдет какую-либо таблицу на странице, предложит ее как источник данных . Очень просто и быстро. Также есть возможность воспользоваться сервисом поиска в общедоступных данных в Интернете, который, однако, пока работает только для американских данных.
После выбора одного из источников данных появится отдельныйокно, в котором вы можете преобразовать данные, а затем сохранить их. При работе с данными в Power Query все ваши действия сохраняются в виде списка последовательных шагов, поэтому вы можете четко видеть, какие преобразования вы выполнили с данными. Здесь у вас есть обзор того, что произошло с данными с тех пор, как вы загрузили их в PQ. Варианты преобразования великолепны: фильтрация строк, удаление дубликатов, разделение столбцов, замена значений, даже преобразование текста и создание дополнительных столбцов. Вы можете создать больше, просто щелкнув элементы в списке или используя новый язык программирования M. После выполнения всех шагов мы можем выбрать, хотим ли мы сохранить результат в листе Excel или в модели данных. В результате весь вопрос сохраняется, поэтому мы можем вернуться к нему в любое время и изменить или добавить то, что в нем не так. В связи с Power BI мы можем поделиться этими вопросами и позволить другим коллегам использовать наши созданные вопросы. Подробнее об этом будет рассказано в одной из следующих работ с упором на Power BI.
Power Query также поддерживает так называемое «свертывание запросов», что значительно ускоряет работу с данными и их загрузку в модель данных. Если мы подключены, например, к MS SQL Server, «свертывание запросов» работает таким образом, что он интерпретирует наши шаги непосредственно в SQL Server и возвращает только те данные, которые нам нужны. Например, когда мы импортируем таблицу из SQL Server в PQ, то фильтруем только некоторые строки, заменяем определенные значения, удаляем ненужные столбцы, а затем сохраняем в модель данных и т. д. Если выбран тот же запрос, эти шаги будут не происходит на нашем компьютере в PQ, но в исходной базе данных будет автоматически создан SQL-запрос, который вернет требуемый результат.Это означает, что запрос загружается быстрее, чем если бы мы хотели больше, даже после загрузки всех данных в PQ.
Язык М
Язык M является процедурным и основан на чем-то вроде C#. Все шаги, которые вы выполняете, также могут быть написаны вручную с использованием этого языка. Он предлагает мощный инструмент для редактирования и обработки данных для самих отчетов. Одним из примеров может быть создание таблицы календаря с помощью Power Query. Достаточно использовать предопределенную функцию List.Dates, которой передаются аргументы, и она сгенерирует список дат календаря. Вы можете разделить этот список для отдельных дней на дополнительные столбцы, такие как названия дней недели, названия месяцев, года и другую информацию, которую можно удалить из таблицы календаря.
Еще одна возможность использования — запрограммированные функции, которые могут вычислять дополнительные значения для таблицы. Эта функция ведет себя как любой другой запрос PQ, она просто ожидает входного значения. Мы можем, например, представить функцию, которая возвращает регион, из которого находится собака, после запрошенной собаки. Мы можем связать этот запрос с таблицей контактов, где у каждого контакта есть заданный адрес. После загрузки данных добавим новую колонку, которая будет вызывать созданную нами функцию и она будет возвращать название региона, на который попадает указанный контакт. Все было по-прежнему только в Excel.
