• Статья
  • Чтение занимает 6 мин

При работе в окне Редактора Power Query в Power BI Desktop можно воспользоваться рядом стандартных задач. В этой статье показаны эти задачи и приведены ссылки для получения дополнительных сведений.

Здесь рассматриваются такие распространенные задачи с запросами:

  • Подключение к данным
  • Формирование и объединение данных.
  • Группирование строк.
  • Сводные столбцы.
  • Создание настраиваемых столбцов.
  • Формулы запросов.

’Для выполнения этих задач мы используем несколько подключений к данным. Данные доступны для загрузки или подключения, если вы хотите самостоятельно выполнить эти задачи.

Первое подключение к данным — это книга Excel, которую можно скачать и сохранить локально. Второе — веб-ресурс, который также используется в других статьях по Power BI Desktop:

https://www.bankrate.com/retirement/best-and-worst-states-for-retirement/

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

Подключение к данным

чтобы подключиться к данным в Power BI Desktop, выберите главная , а затем — получить данные. В Power BI Desktop откроется меню с наиболее распространенными источниками данных. Чтобы открыть полный список источников данных, к которым может подключаться Power BI Desktop, выберите Больше в конце меню. Дополнительные сведения см. в статье Источники данных в Power BI Desktop.

Most Common data sources menu, Get data button, Power BI Desktop

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

Excel data source, Navigator dialog box, Get Data, Power BI Desktop

Вы можете нажать кнопку Преобразовать данные, чтобы изменить, скорректировать или сформировать данные перед загрузкой в Power BI Desktop. Редактирование особенно полезно при работе с большими наборами данных, которые требуется сократить перед загрузкой.

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

Web data source, Get Data dialog box, Power BI Desktop

Отобразится диалоговое окно Из Интернета, где можно ввести URL-адрес веб-страницы.

From Web dialog box, Web data source, Get Data, Power BI Desktop

Выберите ОК. Как и ранее, Power BI Desktop изучает данные веб-страницы и отображает параметры предварительного просмотра в диалоговом окне Навигатор. При выборе таблицы данные отображаются в режиме предварительного просмотра.

Другие подключения к данным выполняются аналогично. Если для подключения к данным требуется аутентификация, в Power BI Desktop отобразится запрос на ввод соответствующих учетных данных.

Пошаговую демонстрацию подключения к данным в Power BI Desktop см. в этой статье.

Формирование и объединение данных.

Вы можете легко формировать и объединять данные с помощью Редактора Power Query. В этом разделе приводится несколько примеров формирования данных. Полную демонстрацию формирования и объединения данных с помощью Power BI Desktop см. в этой статье.

В предыдущем разделе вы подключались к двум наборам данных: книге Excel и веб-ресурсу. После загрузки данных в Редактор Power Query выберите запрос веб-страницы из доступных запросов в области Запросы, как показано ниже.

Queries pane, Power Query Editor, Power BI Desktop

При формировании данных источник данных преобразуется в нужные форму и формат.

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

Remove Columns command, Power Query Editor, Power BI Desktop

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

Группирование строк.

В Редакторе Power Query можно группировать значения из нескольких строк в одно значение. Это полезно при суммировании количества продуктов, общего объема продаж или количества учащихся.

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

Давайте посмотрим ’ , сколько агентств имеет каждое состояние. (В том числе школьных округов и других образовательных учреждений, таких как региональные службы и т. д.) Выберите идентификатор агентства-Нцес Assigned [округ] последний доступный год , а затем нажмите кнопку Группировать по на вкладке Преобразование или на вкладке Главная ленты. (Кнопка Группировать по есть на обеих вкладках.)

Screenshot shows how to group rows in a table.

Откроется диалоговое окно Группировать по. Когда Редактор Power Query группирует строки, он создает новый столбец, в который помещаются результаты выполнения команды Группировать по. Скорректировать операцию Группировать по можно следующими способами.

  1. В раскрывающемся списке указывается столбец для группирования. Редактор Power Query выбирает столбец по умолчанию, но его можно изменить на любой столбец в таблице.
  2. Имя нового столбца: Редактор Power Query автоматически предлагает имя для нового столбца с учетом операции, которая применяется к группируемому столбцу. Однако этому новому столбцу можно присвоить любое имя.
  3. Operation: вы можете выбрать операцию, которую применяет Редактор Power Query, например Сумма, Медиана или Посчитать уникальные строки. Значение по умолчанию — Считать строки.
  4. Добавление группирования и Добавление агрегирования: эти кнопки доступны, только если выбран параметр Дополнительно. С помощью этих кнопок в рамках одной операции можно выполнять операции группирования (действия Группировать по) с несколькими столбцами и создавать несколько агрегатов. С учетом выбранных в этом диалоговом окне значений Редактор Power Query создает столбец, который работает с несколькими столбцами.

Нажмите кнопку Добавление группирования или Добавление агрегирования, чтобы добавить группирования или агрегаты в операцию Группировать по. Чтобы удалить группирование или агрегат, щелкните значок многоточия ( ) справа от строки, а затем выберите Удалить. Попробуйте выполнить операцию Группировать по со значениями по умолчанию, чтобы увидеть, что произойдет.

Screenshot shows the Group By dialog box with Add grouping and Add aggregation called out.

Когда вы нажмете кнопку ОК, Редактор Power Query выполнит операцию Группировать по и возвратит результаты. Итак, Взгляните на – Огайо, Иллинойс, Техас и Калифорния, теперь у каждого из них есть тысячи учреждений!

Count column, Group By operation, Power Query Editor, Power BI Desktop

C помощью Редактора Power Query всегда можно удалить последнюю операцию формирования. В области Параметры запроса в разделе Примененные шаги просто щелкните X рядом с только что завершенным действием. Поэкспериментируйте. Если вы не хотите, чтобы результаты были выбраны, повторяйте ’ шаг до тех пор, пока Power Query редактор не предпринял бы нужные данные.

Сводные столбцы.

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

Давайте рассмотрим ’ пример. Таблица Products_by_Categories сформирована так, что в ней отображаются только уникальные продукты (по имени) и указано, к какой категории продуктов они относятся. Чтобы создать новую таблицу, отображающую количество продуктов для каждой категории (на основе столбца CategoryName ), выберите столбец, а затем выберите преобразоватьсводный столбец.

Pivot Column command, Power Query Editor, Power BI Desktop

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

Pivot Column dialog box, Power Query Editor, Power BI Desktop

При нажатии кнопки ОК, запрос отображает таблицу в соответствии с инструкциями преобразования, предоставленными в диалоговом окне Столбец сведения.

Pivot Column result, Power Query Editor, Power BI Desktop

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

В Редакторе Power Query можно создавать настраиваемые формулы, которые работают по нескольким столбцам в таблице. Затем результаты таких формул можно поместить в новый (настраиваемый) столбец. Редактор Power Query упрощает создание настраиваемых столбцов.

Используя данные книги Excel в Редакторе Power Query, перейдите на вкладку ленты Добавить столбец, а затем выберите Настраиваемый столбец.

Add Custom Column command, Power Query Editor, Power BI Desktop

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

Custom Column dialog box, Power Query Editor, Power BI Desktop

Как и в случае с любым другим примененным действием в редакторе Power Query, если новый настраиваемый столбец не ’ предоставляет данные, которые вы ’ ищете повторно, этот шаг можно удалить. В области Параметры запроса в разделе Примененные шаги просто щелкните X рядом с действием Добавлен пользовательский объект.

Applied steps, Query Settings pane, Power Query Editor, Power BI Desktop

Формулы запросов.

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

Formula Bar option, Power Query Editor, Power BI Desktop

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

Advanced Editor command, Power Query Editor, Power BI Desktop

Здесь представлен Расширенный редактор с действиями запроса, связанными с отображаемым запросом USA_StudentEnrollment. Эти действия создаются на языке формул Power Query, который часто называют M. Дополнительные сведения см. в статье Формулы Power Query. См. дополнительные сведения о спецификации языка Power Query M.

Advanced Editor dialog box, Power Query Editor, Power BI Desktop

Power BI Desktop предоставляет широкий набор категорий формул. Дополнительные сведения и полный перечень всех формул Редактора Power Query см. в этой статье.

Дальнейшие действия

Power BI Desktop предоставляет широкие возможности. Дополнительные сведения об этих возможностях см. в следующих ресурсах.

  • Что такое Power BI Desktop?
  • Об использовании Редактора запросов в Power BI Desktop
  • Источники данных в Power BI Desktop
  • Подключение к данным в Power BI Desktop
  • Формирование и объединение данных в Power BI Desktop