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

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

Полезно знать, что в редакторе запросов в Power BI Desktop можно в полной мере использовать контекстные меню в дополнение к задачам, доступным на ленте. Почти все действия, которые можно выбрать на ленте Преобразование , также доступны, если щелкнуть правой кнопкой мыши элемент (например, столбец) и выбрать соответствующий пункт в появившемся меню.

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

При формировании данных в редакторе запросов вы создаете пошаговые инструкции (которые автоматически выполняются в этом редакторе запросов) для настройки данных по мере их загрузки и отображения в редакторе. Это не влияет на исходный источник данных; корректируется или *формируется*только это конкретное представление данных.

Указываемые действия (переименование таблицы, преобразование типа данных или удаление столбцов) записываются редактором запросов, и каждый раз, когда этот запрос подключается к источнику данных, эти действия выполняются, чтобы данные всегда были сформированы указанным образом. Это происходит всякий раз, когда вы используете функцию редактора запросов Power BI Desktop или когда кто-либо другой использует ваш предоставленный в общий доступ запрос, например в службе Power BI . Эти действия последовательно записываются в области Параметры запроса в разделе Примененные действия.

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

Сформируем данные об увольнениях из раздела Начало работы с Power BI Desktop, которые мы нашли, подключившись к источнику данных в Интернете, нужным нам образом.

Для начинающих отметим следующее: оценки в одном столбце не были автоматически преобразованы из текста в числа при загрузке таблицы в редакторе запросов, а нам нужно, чтобы это были числа. Чтобы внести нужные изменения, достаточно щелкнуть правой кнопкой мыши заголовок столбца и выбрать Изменить тип > Целое число. Чтобы выбрать несколько столбцов, сначала выделите столбец, нажмите и удерживайте клавишу SHIFT, одновременно выделите смежные столбцы, а затем щелкните правой кнопкой мыши заголовок столбца, чтобы изменить все выделенные столбцы. Можно также использовать клавишу CTRL , чтобы выбрать несмежные столбцы.

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

Обратите внимание, что в области Параметры запросав разделе Примененные действия отражаются все примененные действия по формированию данных. Если требуется удалить какой-либо шаг из процесса формирования, можно просто щелкнуть значок X слева от него. На следующем рисунке в разделе Примененные действия отражаются предпринятые до этого момента действия: подключение к веб-сайту (Источник); выбор таблицы (Навигация); автоматическое изменение редактором запросов при загрузке таблицы типа текстовых столбцов, содержащих числа, с текстового на целочисленный (Изменен тип). Тип одного столбца рейтингов не был автоматически изменен на числовой, и в следующих нескольких абзацах мы объясним, почему.

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

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

  • Устранить несколько ошибок: один из столбцов, Качество здравоохранения, содержит несколько связей в рейтингах штатов, которые на веб-сайте отмечались с помощью текста (связь) после чисел. Это хорошо работало на веб-сайте, но теперь нам нужно преобразовать этот столбец вручную. В Power BI Desktop это сделать очень легко. Кроме того, так нам удастся продемонстрировать замечательную функцию Примененные действия в запросе.

  • Изменить имя таблицы : имя Таблица 0 не несет полезных сведений, но его легко изменить.

Чтобы удалить первый столбец, просто выберите столбец, перейдите на вкладку Главная на ленте, а затем выберите Удалить столбцы , как показано на следующем рисунке.

Далее нам нужно обратиться к текстовому столбцу и преобразовать его в числовой. На первый взгляд кажется, что изменить тип столбца Качество здравоохранения с текстового на числовой (например, Целое число*или *Десятичное число) нетрудно. Но если мы изменим тип с текстового ( Текст ) на целочисленный ( Целое число), а затем просмотрим значения в этом столбце, то обнаружим, что редактор запросов выдал несколько ошибок.

Существует несколько способов получить дополнительные сведения о каждой ошибке. Можно выбрать ячейку, не щелкая слово Ошибка, или щелкнуть непосредственно слово Ошибка . Если выбрать ячейку, не щелкая непосредственно слово Ошибка, то редактор запросов отобразит сведения об ошибке в нижней части окна.

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

Чтобы вернуться в редактор запросов, необходимо удалить этот шаг, щелкнув X рядом с ним.

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

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

Итак, теперь эти значения можно исправить, а затем изменить тип. Поскольку редактор запросов записывает действия последовательно, но независимо друг от друга, можно перемещать каждое Примененное действие вверх или вниз в последовательности. Просто щелкните правой кнопкой какое-либо действие, и редактор запросов предоставит меню, которое позволяет делать следующее: Переименовать, Удалить, Удалить до конца (удалить все действия, начиная с текущего), Переместить вверхили Переместить вниз.

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

Сначала выберем Примененное действие до изменения типа столбца Качество здравоохранения . Затем мы изменим в ячейках значения, в которых имеется текст «(tie)», чтобы осталось только число. Щелкните правой кнопкой мыши ячейку, содержащую значение "35 (связь)", и выберите в появившемся меню команду Заменить значения... . Обратите внимание, какое Примененное действие выбрано в данный момент (действие до изменения типа).

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

Имеется три ячейки с (tie), поэтому мы заменим значения в каждой из них. При создании нового примененного действия редактор запросов присваивает ему новое имя с учетом действия — в данном случае Заменено значение. Если в запросе появляется еще одно действие с тем же именем, редактор запросов добавляет к нему последовательный номер, чтобы различать Примененные действия .

На следующем рисунке показано, что в разделе Параметры запроса имеется три действия Заменено значение, но также показано нечто еще более интересное: поскольку мы удалили из столбца Качество здравоохранения все экземпляры текста "(связь)", действие Изменен тип теперь завершается без ошибок.

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

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

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

Изменить имя таблицы легко: в области Параметры запроса в разделе Свойствавведите новое имя таблицы, как показано на следующем рисунке, и нажмите клавишу Ввод. Давайте назовем эту таблицу RetirementStats.

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

Объединение данных

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

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

http://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations

На ленте Главная в редакторе запросов мы выбираем Создать источник > Интернет, вводим адрес, нажимаем кнопку "ОК". В навигаторе отобразится то, что найдено на этой веб-странице.

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

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

Чтобы придать данным нужную форму, выполним перечисленные ниже действия.

  • Удалим первые две строки — они появились как следствие способа, которым была создана таблица на веб-странице, и не нужны нам. На ленте Главная выберите Сократить строки > Удалить строки > Удалить верхние строки.

Откроется окно Удалить верхние строки , в котором можно указать, сколько строк требуется удалить.

  • Удалим последние 26 строк — это все территории, которые нам не нужно включать. На ленте Главная выберите Сократить строки > Удалить строки > Удалить нижние строки.

  • Поскольку таблица RetirementStats не содержит сведения для Washington DC (Вашингтон, округ Колумбия), нам необходимо отфильтровать их из нашего списка. Выберите стрелку раскрывающегося списка рядом со столбцом Region Status (Состояние региона), а затем снимите флажок рядом с элементом Federal district(Федеральный округ).

  • Удалим несколько ненужных столбцов — нам требуется только сопоставление штата с его официальным двухбуквенным кодом, поэтому можно удалить следующие столбцы: Column2, Column3, а затем с Column5 по Column10. Сначала выберите Column2, затем нажмите и удерживайте клавишу CTRL и выберите остальные столбцы для удаления (с помощью этой клавиши можно выбирать несколько несмежных столбцов). На вкладке "Главная" на ленте выберите Удалить столбцы > Удалить столбцы.

  • Используем первую строку в качестве заголовков — поскольку мы удалили три верхние строки, текущая верхняя строка является нужным нам заголовком. Можно выбрать Use First Row As Headers (Использовать первую строку как заголовки) на вкладке Главная или на вкладке Преобразование ленты.

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

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

  • Переименуйте столбцы и саму таблицу. Как обычно, существует несколько способов переименования столбца: сначала выберите столбец, а затем выберите команду Переименовать на вкладке Преобразование ленты или щелкните правой кнопкой мыши и выберите команду Переименовать... в появившемся меню. На следующем рисунке оба варианта показаны стрелками; просто выберите один из них.

Давайте переименуем их в Имя штата и Код штата. Чтобы переименовать таблицу, просто введите имя в поле Имя в области Параметры запроса . Давайте назовем эту таблицу StateCodes.

Теперь, когда таблица StateCodes сформирована нужным образом, давайте объединим эти две таблицы (или два запроса) в одну; поскольку таблицы, которые у нас получились, представляют собой результат применения запросов к данным, они часто называются запросами.

Существует два основных способа объединения запросов: слияние и дополнение.

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

В данном случае мы хотим слить запросы. Для начала в левой области редактора запросов мы выберем запрос, с которым хотим объединить другой запрос, которым в данном случае является RetirementStats. Затем на вкладке Главная на ленте выберите Объединить > Объединить запросы.

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

Появится окно Слияние , предлагающее выбрать таблицу для слияния с выбранной и соответствующие столбцы для слияния. Выберите столбец "Штат" из таблицы (запроса) RetirementStats , затем выберите запрос StateCodes (в данном случае это просто, так как существует только один другой запрос, а при подключении к нескольким источникам данных приходится выбирать из нескольких запросов). Выбрав нужные сопоставляемые столбцы ( Штат из таблицы RetirementStats*и Название штата из таблицы *StateCodes , мы увидим окно Слияние , которое выглядит следующим образом, и активную кнопку ОК .

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

Чтобы развернуть слитую таблицу и выбрать столбцы для включения, щелкните значок развертывания (Expand ). Появится окно Развернуть .

В данном случае нам нужен только столбец Код штата , поэтому мы выберем только этот столбец, а затем нажмем кнопку ОК. Мы снимаем флажок "Использовать имя исходного столбца как префикс", поскольку нам это не нужно; если оставить этот флажок, то объединенный столбец будет называться NewColumn.State Code (имя исходного столбца NewColumn, затем точка, а затем имя столбца, отображаемого в запросе).

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

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

Чтобы применить изменения и закрыть редактор запросов, выберите "Закрыть и применить" на вкладке Главная ленты. Преобразованный набор данных отображается в Power BI Desktop, готовый к использованию для создания отчетов.

Дополнительные сведения

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