Выполнение распространенных задач запросов в Power BI Desktop

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

Ниже приведены распространенные задачи запроса:

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

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

Первое подключение к данным — рабочая книга Excel, скачиваемая и сохраняемая локально.

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

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

Чтобы подключиться к данным в Power BI Desktop, выберите Главная, а затем выберите Получить данные. Power BI Desktop представляет меню с наиболее распространенными источниками данных. Чтобы получить полный список источников данных, к которым может подключиться Power BI Desktop, выберите пункт Ещё в конце меню. Дополнительные сведения см. в источниках данных в Power BI Desktop .

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

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

Подключение к разным типам данных — это аналогичный процесс. Чтобы подключиться к источнику веб-данных, выберите Получить данные>Дополнительно, а затем выберите Другие>Веб->Подключение.

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

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

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

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

Формировать и объединять данные

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

В этом разделе и в следующих разделах используется пример книги Excel , упомянутый ранее, который можно скачать и сохранить локально. Загрузите данные в редакторе Power Query с помощью кнопки Преобразовать данные на вкладке Главная. После загрузки данных выберите Таблицу 1 из доступных запросов в области Запросы, как показано здесь.

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

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

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

Группировать строки

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

В этом примере вы группируете строки в семантической модели записи на обучение. Данные из книги Excel.

В этом примере показано, сколько агентств имеет каждое государство. (Учреждения могут включать школьные округа, другие образовательные учреждения, такие как региональные сервисные округа, и многое другое.) Выберите столбец состояния , а затем нажмите кнопку "Группировать по " на вкладке «Преобразование» или на вкладке «Главная» ленты. (Группировка по доступна в обеих вкладках.)

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

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

Выберите Добавить группирование или Добавить агрегирование, чтобы добавить дополнительные группировки или агрегаты в операцию Group By. Чтобы удалить группирование или агрегирование, щелкните значок с многоточием (...) справа от строки, а затем Удалить. Используйте операцию Group By с помощью значений по умолчанию, чтобы увидеть, что происходит.

При выборе ОКредактор Power Query выполняет операцию группировка по и возвращает результаты.

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

Поворотные столбцы

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

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

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

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

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

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

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

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

Как и в любом другом примененном шаге в редакторе Power Query, если новый пользовательский столбец не предоставляет нужные данные, можно удалить этот шаг. В области параметров запроса в разделе ПРИМЕНЕННЫЕ ШАГИпросто выберите X рядом с шагом Добавленные настраиваемые.

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

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

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

Вот снимок экрана расширенного редактора , с шагами запроса, связанными с запросом США_StudentEnrollment, отображённым. Эти шаги создаются на языке формул Power Query, который часто называется M. Дополнительные сведения см. в статье Создание формул Power Query в Excel. Сведения о самой спецификации языка см. в спецификации языка Power Query M.

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

Связанное содержимое

Вы можете выполнять все действия с помощью Power BI Desktop. Дополнительные сведения о его возможностях см. в следующих ресурсах:

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