Полное* руководство по Power Query

Оригинал статьи: https://www.howtoexcel.org/the-complete-guide-to-power-query/

(* по мнению автора:) (прим.перев.))

Что такое Power Query?

Power Query - это инструмент бизнес-аналитики, доступный в Excel, который позволяет импортировать данные из множества различных источников, а затем очищать, преобразовывать и изменять форму ваших данных по мере необходимости.

Он позволяет вам настроить запрос один раз, а затем повторно использовать его с простым обновлением. Он также довольно мощный. Power Query может импортировать и очищать миллионы строк в модель данных для последующего анализа. Пользовательский интерфейс интуитивно понятен и хорошо продуман, поэтому его действительно легко освоить. Это невероятно короткий путь обучения по сравнению с другими инструментами Excel, такими как формулы или VBA.

Самое приятное в этом то, что вам не нужно изучать или использовать какой-либо код, чтобы выполнить что-либо из этого. Редактор power query записывает все ваши преобразования шаг за шагом и преобразует их в M-код для вас, аналогично тому, как работает Macro recorder с VBA.

Если вы хотите отредактировать или написать свой собственный M-код, вы, конечно, можете, но вам определенно не нужно этого делать.

Ссылка на файлы: https://disk.yandex.ru/d/8jURCWGP2hipgA

Получите данные, используемые в этом посте, чтобы следовать дальше.

Что может сделать Power Query?

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

Каждый месяц вам нужно заходить в папку, в которую загружен файл, открывать файл и копировать содержимое в Excel.

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

Система выводит только идентификатор продавца, поэтому вам нужно добавить новый столбец к данным и использовать VLOOKUP, чтобы получить имя продавца, связанное с каждым идентификатором. Затем вам нужно суммировать продажи по продавцам и рассчитать комиссию для выплаты.

Вам также необходимо связать идентификатор продукта с категорией продукта, но только первые 4 цифры кода продукта относятся к категории продукта. Вы создаете еще один столбец, используя функцию LEFT, чтобы получить первые 4 цифры кода продукта, затем используете VLOOKUP для этого, чтобы получить категорию продукта. Теперь вы можете суммировать данные по категориям.

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

С Power Query все это может быть автоматизировано нажатием кнопки обновления ежемесячно. Все, что вам нужно сделать, это создать запрос один раз и повторно использовать его, экономя час работы каждый месяц!

Где находится Power Query?

Power Query доступна в качестве надстройки для загрузки и установки для Excel 2010 и 2013 и появится в виде новой вкладки на ленте с надписью Power Query. В 2016 году он был переименован в Get & Transform и отображается на вкладке Данные без необходимости устанавливать какие-либо надстройки.

Импорт ваших данных с помощью Power Query

Импортировать ваши данные с помощью Power Query очень просто. Excel предоставляет множество распространенных подключений к данным, которые доступны на вкладке "Данные" и которые можно найти с помощью команды "Получить данные".

• Получите данные из одного файла, такого как рабочая книга Excel, текстовый файл или CSV-файл, файлы XML и JSON. Вы также можете импортировать несколько файлов из заданной папки.
• Получайте данные из различных баз данных, таких как SQL Server, Microsoft Access, Analysis Services, SQL Server Analysis Server, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata и баз данных SAP HANA.
• Получение данных из Microsoft Azure
• Получайте данные из онлайн-служб, таких как Sharepoint, Microsoft Exchange, Dynamics 365, Facebook и Salesforce.
• Получайте данные из других источников, таких как таблица или диапазон внутри текущей книги, из Интернета, Microsoft Query, Hadoop, OData feed, ODBC и OLEDB.
• Мы можем объединить два запроса вместе аналогично объединению двух запросов в SQL.
• Мы можем добавить запрос к другому запросу, аналогично объединению двух запросов в SQL.

Примечание: Доступные параметры подключения к данным зависят от вашей версии Excel.

Существует пара наиболее распространенных типов запросов, доступных на верхнем уровне команд ленты, которые находятся в разделе "Получение и преобразование" на вкладке "Данные". Отсюда мы можем легко получить доступ к запросам из текста / CSV, из Интернета и из таблицы / диапазона. Они просто дублируются за пределами команды Get Data для удобства использования, поскольку вы, вероятно, будете использовать их чаще.

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

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

Простой пример импорта данных в файл Excel

Давайте посмотрим на импорт некоторых данных из книги Excel в действии. Мы собираемся импортировать файл Excel под названием Продажи канцелярских товаров Data.xlsx . Оно содержит данные о продажах на одном листе, называемом Данные о продажах, и данные о клиентах на другом листе, называемом Данные о клиентах. Оба листа данных начинаются с ячейки A1, а первая строка данных содержит заголовки столбцов.

Перейдите на вкладку Данные и выберите команду Получить данные в разделе Получение и преобразование данных. Затем перейдите в "Из файла" и выберите "Из книги".

Откроется меню выбора файла, в котором вы сможете перейти к файлу, который хотите импортировать. Выберите файл и нажмите кнопку Импортировать.

После выбора файла, который вы хотите импортировать, откроется окно "Навигатор предварительного просмотра данных". Это даст вам список всех объектов, доступных для импорта из книги. Установите флажок, чтобы выбрать несколько элементов, поскольку мы будем импортировать данные с двух разных листов. Теперь мы можем проверять как данные о клиентах, так и данные о продажах.

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

Когда вы убедитесь, что у вас есть все необходимое из рабочей книги, вы можете нажать кнопки Редактировать или Загрузить. Кнопка редактировать приведет вас к редактору запросов, где вы сможете преобразовать свои данные перед их загрузкой. Нажатие кнопки загрузить загрузит данные в таблицы на новых листах рабочей книги.

В этом простом примере мы обойдем редактор и сразу перейдем к загрузке данных в Excel. Нажмите маленькую стрелку рядом с кнопкой Загрузить, чтобы получить доступ к параметрам Загрузить в. Это даст вам еще несколько вариантов загрузки.

Мы выберем загрузку данных в таблицу на новом листе, но есть несколько других вариантов. Вы также можете загрузить данные непосредственно в сводную таблицу или диаграмму, или вы можете избежать загрузки данных и просто создать подключение к данным.

Теперь таблицы загружены на новые листы в Excel, и у нас также есть два запроса, которые можно быстро обновить, если данные в исходной книге когда-либо будут обновлены.

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

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

1. Лента – пользовательский интерфейс редактора очень похож на Excel и использует визуальный центр управления в стиле ленты. Он организует команды преобразования данных и другие параметры Power query на 5 основных вкладках.
2. Список запросов – в этой области перечислены все запросы в текущей книге. Вы можете перейти к любому запросу из этой области, чтобы начать его редактирование.
3. Предварительный просмотр данных – в этой области вы увидите предварительный просмотр данных со всеми применяемыми в данный момент шагами преобразования. Вы также можете получить доступ ко многим командам преобразования здесь либо с помощью значков фильтров в заголовках столбцов, либо щелкнув правой кнопкой мыши по заголовку столбца.
4. Строка формул – здесь вы можете просмотреть и отредактировать M-код текущего шага преобразования. Каждое преобразование, которое вы производите с вашими данными, записывается и отображается как шаг в области примененных шагов.
5. Свойства – здесь вы можете назвать свой запрос. Когда вы закрываете и загружаете запрос в таблицу Excel, power query создаст таблицу с тем же именем, что и исходный запрос, если имя таблицы еще не занято. Название запроса - это также то, как M-код будет ссылаться на этот запрос, если нам понадобится запросить его в другом запросе.
6. Примененные шаги – Эта область представляет собой хронологический список всех шагов преобразования, которые были применены к данным. Вы можете перемещаться по шагам здесь и просматривать изменения в области предварительного просмотра данных. Вы также можете удалить, изменить или изменить порядок выполнения любых шагов в запросе здесь.

Список запросов

Список запросов обладает другими возможностями, помимо простого перечисления всех запросов текущей книги.

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

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

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

Если щелкнуть правой кнопкой мыши по любому запросу в списке, будет доступно множество опций.

• Копировать и вставлять – Скопируйте и вставьте запрос, чтобы сделать его другую копию.
• Удалить – удалить запрос. Если вы случайно удалили запрос, кнопки отменить нет, но вы можете выйти из редактора запросов без сохранения с помощью close и load, чтобы восстановить свой запрос.
• Переименовать – переименуйте свой запрос. Это то же самое, что переименовать его из раздела свойств в левой части редактора.
• Дублировать – создайте еще одну копию запроса. Это то же самое, что копировать и вставлять, но превращает процесс в один шаг.
• Перейти к группированию – поместите ваши запросы в структуру, подобную папке, чтобы сохранить их упорядоченными, когда список станет большим.
• Перемещение вверх и вниз – измените порядок отображения запросов в списке или в группах папок, чтобы добавить их к вашим организационным усилиям. Это также можно сделать, перетащив запрос в новое местоположение.
• Создать функцию – превратите ваш запрос в функцию запроса. Они позволяют передавать параметр в запрос и возвращать результаты на основе переданного параметра.
• Преобразовать в параметр – позволяет преобразовывать параметры в запросы или запросы в параметры.
• Расширенный редактор – Откройте расширенный редактор, чтобы отредактировать M-код для запроса.
• Свойства – позволяет изменить название запроса, добавить текст описания и включить опцию быстрой загрузки данных для запроса.

Если вы щелкните правой кнопкой мыши на любой пустой области в списке запросов, вы можете создать новый запрос.

Предварительный просмотр данных

Основная задача области предварительного просмотра данных - применить шаги преобразования к вашим данным и показать предварительный просмотр этих шагов, которые вы применяете.

В области предварительного просмотра данных вы можете выбирать столбцы несколькими различными способами. При выборе столбец будет выделен светло-зеленым цветом.

• Выберите один столбец, щелкнув левой кнопкой по заголовку столбца.
• Выберите несколько смежных столбцов, щелкнув левой кнопкой мыши по заголовку первого столбца, затем, удерживая Shift, щелкните левой кнопкой мыши по заголовку последнего столбца.
• Выберите несколько несмежных столбцов, удерживая Ctrl, затем щелкните левой кнопкой по любым заголовкам столбцов, которые вы хотите выбрать.

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

В левой части заголовка столбца у каждого столбца есть значок типа данных. Вы можете щелкнуть левой кнопкой мыши на нем, чтобы изменить тип данных столбца.

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

Использование опции Locale позволяет задавать формат типа данных с использованием соглашения из разных мест. Например, если вы хотите отобразить дату в американском формате m / d /yyyy вместо обычного dd / mm /yyyy, вы могли бы выбрать Соединенные Штаты в качестве локали.

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

Переименовать заголовок любого столбца действительно просто. Дважды щелкните левой кнопкой мыши на заголовке любого столбца, затем введите свое новое имя и нажмите Enter, когда закончите.

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

В правой части каждого столбца также есть переключатель фильтра. Щелкнителевой кнопкой мыши на нем, чтобы отсортировать и отфильтровать ваши данные. Это меню фильтров очень похоже на фильтры, найденные в обычной электронной таблице, и будет работать таким же образом.

Показанный список элементов основан на выборке данных, поэтому может содержать не все доступные элементы в данных. Вы можете загрузить больше, нажав на текст, выделенный синим цветом, Загрузить больше.

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

Применяемые шаги

Любое преобразование, которое вы внесете в свои данные, будет отображаться в виде шага в области "Примененные шаги". Это также позволяет вам перемещаться по вашему запросу. Щелкните левой кнопкой мыши на любом шаге, и предварительный просмотр данных обновится, чтобы показать все преобразования до этого шага включительно.

Вы можете вставить новые шаги в запрос в любой момент, выбрав предыдущий шаг, а затем создав преобразование в окне предварительного просмотра данных. Затем Power Query спросит, хотите ли вы вставить этот новый шаг. Однако будьте осторожны, так как это может нарушить выполнение следующих шагов, которые относятся к чему-то, что вы изменили.

Вы можете удалить все шаги, которые были применены, используя крестик с левой стороны от названия шага в области "Примененные шаги". Однако необходимо соблюдать осторожность, поскольку, если любой из следующих шагов зависит от шага, который вы пытаетесь удалить, вы нарушите выполнение запроса. Вот где может быть удобно Удалить до конца из контекстного меню правой кнопки мыши.

Множество шагов преобразования, доступных в power query, будут иметь различные параметры пользовательского ввода и другие настройки, связанные с ними. Если вы примените фильтр к столбцу product, чтобы отобразить все товары, не начинающиеся с Pen, позже вы можете решить, что вам нужно изменить этот шаг фильтрации, чтобы отобразить все товары, не равные Pen. Вы можете внести эти изменения в области Примененный шаг.

На некоторых шагах справа будет отображаться маленький значок шестеренки. Это позволяет редактировать входные данные и настройки этого шага.

Вы можете изменить порядок выполнения шагов в вашем запросе. Просто щелкните левой кнопкой мыши на любом шаге и перетащите его в новое место. Зеленая линия между шагами укажет новое местоположение. Это еще один вариант, с которым вам нужно быть осторожным, поскольку многие шаги будут зависеть от предыдущих шагов, и изменение порядка может привести к ошибкам из-за этого.

Щелкните правой кнопкой мыши на любом шаге, чтобы получить доступ к меню опций.

• Редактировать настройки – это позволяет редактировать настройки шага аналогично использованию значка шестеренки в правой части шага.
• Переименовать – это позволяет вам переименовать метку шагов. Вместо отображения общего названия типа “Отфильтрованные строки“ вы могли бы отобразить что-то вроде “Отфильтрованные строки продуктов на ручках”, чтобы вы могли легко определить, что выполняется на шаге.
• Удалить – при этом удаляется текущий шаг, аналогичный крестику в левой части шага.
• Удалить до конца – это позволяет удалить текущий шаг плюс все шаги до конца. Поскольку шаги могут зависеть от предыдущих шагов, удаление всех шагов после шага - хороший способ избежать любых ошибок.
• Вставить шаг после – это позволяет вставить новый шаг после текущего шага.
• Перемещение вверх и вниз – это позволяет вам изменять порядок выполнения шагов запроса, аналогично методу перетаскивания.
• Извлечь предыдущий – это может быть действительно полезной опцией. Это позволяет вам создать новую копию запроса вплоть до выбранного шага.

Строка формул

Когда вы нажимаете на различные этапы процесса преобразования в области Примененные шаги, строка формул обновляется, отображая M-код, созданный для этого шага. Если сгенерированный код M длиннее строки формул, вы можете развернуть строку формул с помощью переключателя со стрелкой на правой стороне.

Вы можете редактировать M-код для шага непосредственно из строки формул без необходимости открывать расширенный редактор. В этом примере мы изменили наш фильтр с “Пера” на “Стул”, введя в строке формул, а затем нажав Ввод или используя флажок слева для подтверждения изменения. Нажмите Esc или используйте X
слева, чтобы отменить все изменения.

Вкладка Файл

Вкладка "Файл" содержит различные опции для сохранения любых изменений, внесенных в ваши запросы, а также параметры и настройки power query.

• Закрыть и загрузить – это сохранит ваши запросы и загрузит ваш текущий запрос в таблицу Excel в рабочей книге.
• Закрыть и загрузить в – откроется меню Импорт данных с различными вариантами загрузки данных на выбор.
• Отменить и закрыть – это отменит все изменения, внесенные вами в запросы во время сеанса работы в редакторе, и закроет редактор.

Обратите внимание, что вам все равно нужно будет сохранить книгу обычным способом, чтобы сохранить любые изменения в запросах, если вы закроете книгу.

Команды Закрыть и загрузить и Закрыть и загрузить в также доступны на вкладке Главная.

Параметры загрузки данных

При использовании опции Закрыть и загрузить в для выхода из редактора откроется меню Импорт данных.

Вы можете загрузить запрос в таблицу, сводную таблицу, сводную диаграмму или создать соединение только для запроса. Параметр "Только подключение" будет означать, что данные не выводятся в рабочую книгу, но вы все равно можете использовать этот запрос в других запросах. Это хороший вариант, если запрос является промежуточным этапом в процессе преобразования данных.

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

Другой вариант, который вы получаете, - это Добавить эти данные в модель данных. Это позволит вам использовать вывод данных в Power Pivot и использовать другие функциональные возможности модели данных, такие как построение связей между таблицами. Модель данных Excel - это новый эффективный способ хранения и использования больших объемов данных.

Окно запросов и подключений

Когда вы работаете вне редактора Power query, вы можете просматривать все запросы в книге и взаимодействовать со всеми ними через окно Запросы и подключения. Чтобы открыть это, перейдите на вкладку "Данные" на обычной ленте Excel, затем нажмите командную кнопку "Запросы и подключения", расположенную в разделе "Запросы и подключения".

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

Это очень похоже на список запросов в редакторе, и вы можете выполнить множество тех же действий, щелкнув правой кнопкой по любому запросу.

Стоит отметить один вариант, которого нет в контекстном меню списка запросов, - это параметр Загрузить в. Это позволит вам изменить параметр загрузки для любого запроса, так что вы сможете изменять любые запросы, связанные только с подключением, для загрузки в таблицу Excel в рабочей книге.

Еще одна вещь, которую стоит отметить, это то, что при наведении курсора мыши на запрос Excel сгенерирует предварительный просмотр данных Peek. Это покажет вам некоторую базовую информацию о запросе.

• Предварительный просмотр данных – это предварительный просмотр данных в реальном времени, аналогичный тому, который был при первой настройке запроса.
• Столбцы – это даст вам список всех столбцов, содержащихся в конечных результатах запроса, вместе с подсчетом количества имеющихся столбцов. Нажатие на любой из них выделит столбец в предварительном просмотре данных.
• Последнее обновление – здесь будет указано, когда данные обновлялись в последний раз.
• Состояние загрузки – показывает, загружены ли данные в таблицу, сводную таблицу, сводную диаграмму или это только подключение.
• Источники данных – здесь будет показан источник данных вместе с подсчетом количества файлов, если вы выполняете запрос "Из папки".
• Просмотр на рабочем листе – нажав на это, вы перейдете к таблице вывода, если запрос загружен в таблицу, сводную таблицу или диаграмму.

Вы также можете получить доступ к этому просмотру Peek, щелкнув правой кнопкой по запросу и выбрав Показать просмотр.

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

Вкладка "Главная"

Вкладка "Главная" содержит все действия, преобразования и настройки, которые повлияют на всю таблицу.

1. Закрыть – отсюда вы можете получить доступ к опциям Закрыть и загрузить и Закрыть и загрузить в. Они также доступны в меню вкладки Файл.
2. Запрос – Вы можете обновить предварительный просмотр данных для текущего запроса или всех подключений к запросу. Вы также можете открыть настройки свойств и расширенный редактор для текущего запроса, а под кнопкой Управление есть опции для удаления, дублирования или ссылки на текущий запрос.
3. Управление столбцами – Вы можете перейти к определенным столбцам и выбрать сохранение или удаление столбцов.
4. Сокращать строки – Вы можете управлять строками данных из этого раздела. Существует множество вариантов либо сохранить определенные строки, либо удалить определенные строки. Сохраните или удалите N верхних строк, N нижних строк, определенный диапазон строк, чередующиеся строки, повторяющиеся строки или строки с ошибками. Для удаления строк доступен только один вариант - удалить пустые строки.
5. Сортировка – Вы можете отсортировать любой столбец в порядке возрастания или убывания.
6. Преобразование – В этом разделе содержится набор полезных опций преобразования.Разделение столбцов – это позволяет разделить данные в столбце на основе разделителя или длины символа.
   Группировать по – это позволяет вам группировать и обобщать ваши данные аналогично Group By в SQL.
   Тип данных – это позволяет изменять тип данных любого столбца.
   Используйте первую строку в качестве заголовков – это позволяет повысить первую строку данных до заголовков столбцов или понизить заголовки столбцов до строки данных.
   Заменить значения – это позволяет вам находить и заменять любое значение из столбца.
   
7. Объединить – В этом разделе содержатся все команды для объединения вашего запроса с другими запросами. Вы можете объединять, добавлять запросы или объединять файлы при работе с запросом "Из папки".
8. Параметры – Power Query позволяет создавать параметры для ваших запросов. Например, при настройке запроса "Из папки" может потребоваться, чтобы путь к папке был параметром, чтобы можно было легко изменить местоположение. Вы можете создавать существующие параметры из этого раздела и управлять ими.
9. Источники данных – В этом разделе содержатся настройки источника данных, включая управление разрешениями для любых источников данных, для доступа к которым требуются пароли.
10. Новый запрос – Вы можете создавать новые запросы из новых источников данных или ранее использованных источников данных из этого раздела.

Разница между вкладками Transform и Add Column

Основная масса всех преобразований доступны в Power запрос может быть получен во вкладке Преобразования (Transform) или Добавить столбец (Add column).

Вам может показаться, что эти две вкладки сильно дублируются. Например, обе вкладки содержат раздел "Из текста" с множеством одинаковых команд. На самом деле это не так, есть тонкая разница!

При использовании команды на вкладке Добавить столбец, которая находится на обеих вкладках, будет создан новый столбец с преобразованными данными, а исходный столбец останется нетронутым. В то время как использование эквивалентной команды на вкладке Transform изменит исходный столбец, и новый столбец не будет создан.

Это критический момент, о котором нужно знать!

Вкладка Преобразование

Разделы вкладки Преобразование.

1. Таблица – В этом разделе содержатся команды, которые преобразуют всю таблицу. Вы можете группировать и агрегировать свой запрос, переводить строки в заголовки, понижать заголовки в строки, переносить свои данные, изменять порядок строк и подсчитывать строки.
2. Любой столбец – В этом разделе содержатся команды, которые будут работать с любым столбцом данных независимо от типа данных. Вы можете изменять тип данных, автоматически определять и изменять тип данных, переименовывать заголовок столбца, находить и заменять значения, заполнять значениями вниз (или вверх) столбца, чтобы заменить любые пробелы или нули значением над ним (или под ним), сводить или отменять вывод столбцов, перемещать столбцы в новое местоположение или преобразовывать столбец в список.
3. Текстовый столбец – в этом разделе содержатся команды для обработки текстовых данных. Вы можете разделять столбцы разделителем, форматировать регистр, обрезать и очищать, объединять два или более столбца вместе, извлекать текст и анализировать объекты XML или JSON.
4. Столбец чисел – в этом разделе содержатся команды для числовых данных. Вы можете выполнять различные агрегации, такие как суммы и средние значения, выполнять стандартные операции алгебры или тригонометрии и округлять числа в большую или меньшую сторону.
5. Столбец даты и времени – в этом разделе содержатся команды для обработки данных даты и времени. Вы можете извлекать информацию из ваших данных о датах, времени и продолжительности.
6. Структурированный столбец – В этом разделе содержатся команды для работы с вложенными структурами данных, например, когда ваш столбец содержит таблицы.

Вкладка "Добавить столбец"

Вкладка "Добавить столбец" содержит множество команд, похожих на вкладку "Преобразовать", но ключевое отличие заключается в том, что они создадут новый столбец с преобразованием.

1. Общие сведения – В этом разделе можно добавлять новые столбцы на основе формул или пользовательских функций. Вы также можете добавить столбцы индекса или дублировать столбец отсюда.
2. Из текста – очень похоже на раздел Из текста на вкладке Преобразование, но эти команды создадут новый столбец с преобразованием.
3. Из числа – очень похоже на раздел Из числа на вкладке Преобразование, но эти команды создадут новый столбец с преобразованием.
4. Из даты и времени – очень похоже на раздел Из даты и времени на вкладке Преобразование, но эти команды создадут новый столбец с преобразованием.

Вкладка Просмотр

Вкладка Просмотр довольно скудна по сравнению с другими вкладками. На ней нет команд преобразования. Большинству пользователей Power Query редко понадобится использовать эту область, но все же есть несколько вещей, о которых стоит знать.

1. Макет – В этом разделе можно отобразить или скрыть панель настроек запроса (которая содержит свойства и применяемые шаги) и строку формул.
2. Предварительный просмотр данных – Этот раздел позволяет отображать или скрывать пробельные символы или превращать шрифт в моноширинный шрифт в области предварительного просмотра данных. Это удобно при работе с данными, разделенными определенным количеством символов.
3. Столбцы – это позволяет вам перейти к определенному столбцу и выбрать его в окне предварительного просмотра данных. Эта команда также доступна на вкладке Главная.
4. Параметры – это позволяет включить параметризацию в источниках данных и на этапах преобразования.
5. Дополнительно – откроется расширенный редактор запросов, в котором отображается код M для запроса. Он также доступен на вкладке Главная.
6. Зависимости – Откроется диаграммное представление зависимостей запроса в рабочей книге.

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

Выводы

Power Query поначалу может показаться сложным для новичка во всем этом, но пользовательский интерфейс очень хорошо продуман и легок для восприятия. Хотя это может быть новым для пользователя, многие концепции уже должны быть знакомы пользователю Excel.

Знакомство со всеми частями редактора и расположением вкладок ленты является важным первым шагом в изучении Power Query и внедрении его в вашу повседневную работу.

Хотя о Power Query еще многое предстоит узнать, на это стоит потратить время. С его помощью можно значительно сэкономить время при выполнении повторяющихся задач по очистке данных и форматированию. Это один из самых мощных и полезных инструментов, который был добавлен в Excel со времен сводных таблиц.

Хотите получить еще больше преимуществ от Power Query? Тогда ознакомьтесь с этими удивительными советами по Power Query, которые помогут вам извлечь из этого максимальную пользу!