Руководство. Формирование и объединение данных в Power BI Desktop

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

В этом руководстве вы узнаете, как:

  • Формирование данных в Редакторе Power Query.
  • Подключение к различным источникам данных.
  • Объединение этих источников данных и создание модели данных для использования в отчетах

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

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

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

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

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

Applied steps in Query Settings

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

  1. На ленте Добавить столбец выберите пункт Настраиваемый столбец, который позволяет добавить пользовательский столбец.

    Select Custom Column

  2. В окне Настраиваемый столбец в поле Имя нового столбцавведите Новый ранг. В поле Настраиваемая формула столбца введите следующие данные:

    ([Cost of living] + [Weather] + [Health care quality] + [Crime] + [Tax] + [Culture] + [Senior] + [#"Well-being"]) / 8
    
  3. Убедитесь, что сообщение о состоянии содержит такой текст: No syntax errors have been detected (Синтаксические ошибки не обнаружены), и нажмите ОК.

    Custom Column page with no snytanx errors

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

    Чтобы выбрать несколько столбцов, сначала выделите столбец, нажмите и удерживайте клавишу SHIFT, одновременно выделите смежные столбцы, а затем щелкните правой кнопкой мыши заголовок столбца. Можно также использовать клавишу CTRL, чтобы выбрать несмежные столбцы.

    Select Whole Number column data

  5. Чтобы преобразовать типы данных столбцов (при этом текущий тип данных преобразуется в другой), выберите Тип данных — текст на ленте Преобразование.

    Select Data Type Text

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

    На следующем рисунке список Примененные шаги отражает добавленные действия на данный момент:

    • Источник: подключение к веб-сайту.

    • Извлеченная таблица из HTML: выбор таблицы.

    • Измененный тип: изменение типа текстовых числовых столбцов с текста на целое число.

    • Добавлен пользовательский объект: добавление пользовательского столбца.

    • Измененный тип 1: последнее примененное действие.

      List of Applied Steps

Корректировка данных

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

  • Скорректируйте рейтинги, удалив столбец.

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

  • Устраните несколько ошибок.

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

  • Отсортируйте данные.

    Следует отсортировать данные — на основе столбцов Новый ранг и Ранг.

  • Замените данные.

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

  • Измените имя таблицы.

    Поскольку Таблица 0 вряд ли является полезным дескриптором для таблицы, мы изменим ее имя.

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

    Select Remove Columns

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

  2. Щелкните действие правой кнопкой мыши. Редактор Power Query предоставляет меню, позволяющее сделать следующее.

    • Переименовать: переименовать действие.
    • Удалить: удалить действие.
    • DeleteUntilEnd: удалите текущий шаг и все последующие шаги.
    • Разместить перед: переместить действие вверх в списке.
    • Разместить после: переместить действие вниз в списке.
  3. Сдвиньте вверх последнее действие, Удаленные столбцы, которое предшествует действию Добавлен пользовательский объект.

    Move up step in Applied Steps

  4. Выберите действие Добавлен пользовательский объект.

    Обратите внимание, что данные отображают ошибку, которую нужно устранить.

    Error result in column data

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

    Error information in Power Query Editor

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

  5. Поскольку нам не нужно отображать сведения об ошибках, выберите Отмена.

  6. Чтобы исправить ошибки, выберите столбец New Rank, затем отобразите формулу данных столбца: установите флажок Строка формул на вкладке Вид.

    Select Formula Bar

  7. Теперь можно удалить параметрСтоимость жизни и уменьшить делитель, изменив формулу:

     Table.AddColumn(#"Removed Columns", "New Rank", each ([Weather] + [Health care quality] + [Crime] + [Tax] + [Culture] + [Senior] + [#"Well-being"]) / 7)
    
  8. Установите зеленую галочку слева от поля формулы или нажмите клавишу ВВОД.

Редактор Power Query заменяет данные измененными значениями, а действие Добавлен пользовательский объект завершается без ошибок.

Примечание

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

  1. Отсортируйте данные на основе столбца Новый ранг. Сначала выберите последний примененный шаг Измененный тип1 для получения последних данных. После этого выберите раскрывающийся список рядом с заголовком столбца Новый ранг и выберите команду Сортировать по возрастанию.

    Sort data in New Rank column

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

  2. Чтобы устранить проблему с сортировкой данных, выберите столбец Новый ранг и измените формулу в строке формул на следующую:

     = Table.Sort(#"Changed Type1",{{"New Rank", Order.Ascending},{"Rank", Order.Ascending}})
    
  3. Установите зеленую галочку слева от поля формулы или нажмите клавишу ВВОД.

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

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

    Select Replace Values for column

  5. Выберите команду Вставить.

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

    Insert Step verification

  6. Измените значение данных на 51.

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

  7. Выберите последнее Примененное действие: Отсортированные строки.

    Обратите внимание, что данные в отношении нового ранжирования по Аризоне изменились. Это связано с тем, что шаг Замененное значение вставлен в нужное место, то есть до шага Добавлен пользовательский объект.

  8. Наконец, мы хотим изменить имя этой таблицы на что-нибудь более осмысленное. В области Параметры запроса в разделе Свойства введите новое имя таблицы, а затем нажмите ВВОД. Давайте назовем эту таблицу RetirementStats.

    Rename table in Query Settings

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

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

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

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

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

  1. На ленте Главная в Редакторе Power Query выберите Новый источник > Интернет.

  2. Введите адрес веб-сайта, сокращающего названия штатов, https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations , а затем выберите Подключить.

    Навигатор отображает содержимое веб-сайта.

    Navigator page

  3. Выберите Коды и сокращения.

    Совет

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

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

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

    Select Remove Top Rows

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

    Примечание

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

  2. Удалите последние 26 строк. Эти строки относятся ко внешним малым территориям США, которые не нужно включать. На ленте Главная выберите Удалить строки > Удалить нижние строки.

    Select Remove Bottom Rows

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

    Clear Federal district check box

  4. Удалите несколько ненужных столбцов. Нам требуется только сопоставление штата с его официальным двухбуквенным кодом, поэтому можно удалить несколько столбцов. Сначала выберите столбец, затем нажмите и удерживайте клавишу CTRL и выберите остальные столбцы для удаления. На вкладке Главная на ленте выберите Удалить столбцы >Удалить столбцы.

    Remove column

    Примечание

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

    Примечание

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

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

    Rename column in Power Query Editor

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

Объединение запросов

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

Есть два основных способа слияния запросов — объединение и добавление.

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

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

  1. В левой области Редактора Power Query выберите запрос, с которым требуется объединить другой запрос. В данном случае это RetirementStats.

  2. Затем на вкладке Главная на ленте выберите Объединить запросы > Объединить запросы.

    Select Merge Queries

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

    Откроется окно Слияние. В нем предлагается выбрать таблицу для слияния с выбранной и соответствующие столбцы для слияния.

  3. Выберите Штат из таблицы RetirementStats, а затем выберите запрос StateCodes.

    При выборе правильных совпадающих столбцов кнопка ОК будет включена.

    Merge window

  4. Щелкните ОК.

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

    NewColumn column

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

    Появится окно Развернуть.

    NewColumn in query

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

    Если оставить флажок Использовать исходное имя столбца как префикс установленным, то объединенный столбец будет называться NewColumn.State Code.

    Примечание

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

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

  7. Чтобы применить изменения и закрыть редактор Power Query, выберите "Закрыть & применить" на вкладке ленты "Главная ".

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

    Select Close & Apply

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

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