Руководство. Формирование и объединение данных в 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. Эти шаги записываются последовательно в области Параметры запроса в разделе ПРИМЕНЕННЫЕ ШАГИ. Мы рассмотрим каждый из этих шагов в этой статье.

Снимок экрана: Редактор Power Query с областью параметров запроса и списком примененных действий.

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

    Снимок экрана: Редактор Power Query с выбранным меню

  2. Вставьте этот URL-адрес в диалоговое окно Из Интернета и нажмите кнопку ОК.

    https://www.fool.com/research/best-states-to-retire
    

    Снимок экрана: диалоговое окно Редактор Power Query Из Интернета с введенным URL-адресом исходной страницы.

  3. В диалоговом окне Навигатор выберите Table 1и выберите Преобразовать данные.

    Снимок экрана: диалоговое окно

  4. Откроется окно Редактор запросов Power BI. Шаги по умолчанию, примененные к настоящему времени, можно просмотреть в области Параметры запроса в разделе ПРИМЕНЕННЫЕ ШАГИ.

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

    Снимок экрана: окно Редактор Power Query с выделенным элементом

  5. Измените имя таблицы с значения по умолчанию Table 1 на Retirement Data, а затем нажмите клавишу ВВОД.

    Снимок экрана: Редактор Power Query, на котором показано, как изменить имя таблицы в параметрах запроса.

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

  7. На ленте Добавление столбца выберите Пользовательский столбец.

    Снимок экрана: лента

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

    ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] + [Weather] ) / 8
    
  9. Убедитесь, что сообщение о состоянии содержит такой текст: No syntax errors have been detected (Синтаксические ошибки не обнаружены), и нажмите ОК.

    Снимок экрана: диалоговое окно настраиваемого столбца Редактор Power Query с новым именем столбца, формулой настраиваемого столбца и отсутствием синтаксических ошибок.

  10. В разделе Параметры запроса в списке ПРИМЕНЕННЫЕ ШАГИ теперь отображается новый добавленный настраиваемый шаг, который мы только что определили.

    Снимок экрана: область

Настройка данных

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

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

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

  • Исправьте все ошибки.

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

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

    Отсортируйте данные на основе столбца Новая оценка и сравните с существующим столбцом Ранжирование .

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

    Мы рассмотрим, как заменить определенное значение и как вставить примененный шаг.

Эти изменения описаны в следующих шагах.

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

    Снимок экрана: меню

    Примечание

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

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

    Снимок экрана: контекстное меню Редактор Power Query

  3. Сдвиньте вверх последнее действие, Удаленные столбцы, которое предшествует действию Добавлен пользовательский объект.

    Снимок экрана: список примененных шагов Редактор Power Query с шагом

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

    Обратите внимание, что в столбце Новая оценка теперь отображается ошибка , а не вычисляемое значение.

    Снимок экрана: Редактор Power Query и столбец Новая оценка, содержащий значения ошибок.

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

    Снимок экрана: Редактор Power Query со столбцом

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

  5. Чтобы исправить ошибки, необходимо изменить два параметра: удалить имя столбца Погода и изменить делитель с 8 на 7. Эти изменения можно внести двумя способами:

    1. Щелкните правой кнопкой мыши шаг Пользовательский столбец и выберите Изменить параметры. Откроется диалоговое окно "Настраиваемый столбец" , которое использовалось для создания столбца "Новая оценка ". Измените формулу, как описано выше, пока она не будет выглядеть следующим образом:

      Снимок экрана: диалоговое окно настраиваемого столбца Редактор Power Query с исправленными ошибками формул.

    2. Выберите столбец Новая оценка , а затем отобразите формулу данных столбца, установив флажок Строка формул на вкладке Вид .

      Снимок экрана: Редактор Power Query со столбцом

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

      = Table.AddColumn(#"Removed Columns", "New score", each ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] ) / 7)              
      

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

    Примечание

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

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

    Снимок экрана: Редактор Power Query со столбцом

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

  7. В разделе ПРИМЕНЕННЫЕ ШАГИ выберите шаг перед настраиваемым столбцом, который является шагом Удаленные столбцы . Здесь мы заменим значение рейтинга стоимости жилья в штате Орегон. Щелкните правой кнопкой мыши соответствующую ячейку, содержащую значение стоимости жилья в штате Орегон, а затем выберите Заменить значения. Обратите внимание на то, какое примененное действие выбрано в данный момент.

    Снимок экрана: окно Редактор Power Query со столбцом

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

    Так как мы вставляем шаг, Редактор Power Query напоминает нам, что последующие шаги могут привести к прерыванию запроса.

    Снимок экрана: диалоговое окно проверки шага вставки Редактор Power Query.

  9. Измените значение данных на 100,0.

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

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

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

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

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

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

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

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

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

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

    Снимок экрана: страница

  3. Выберите Коды и сокращения для штатов США, федеральных округов, территорий и других регионов.

    Совет

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

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

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

    Снимок экрана: Редактор Power Query выделения раскрывающегося списка

    Откроется диалоговое окно Удаление верхних строк . Укажите 1 строку для удаления.

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

  3. Так как таблица данных выхода на пенсию не содержит сведений о Вашингтоне или территориях, нам нужно отфильтровать их из нашего списка. Установите раскрывающийся список Имя и состояние столбца region_1 , а затем снимите все флажки, кроме Состояния.

    Снимок экрана: Редактор Power Query фильтра столбцов с выбранным значением

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

    Снимок экрана: Редактор Power Query выделения раскрывающегося списка

    Примечание

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

    Примечание

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

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

    Снимок экрана: Редактор Power Query выделена кнопка

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

    Снимок экрана: окно Редактор Power Query с результатами формирования исходных данных кодов состояний в таблицу.

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

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

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

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

В этом случае мы хотим объединить запросы:

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

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

    Снимок экрана: раскрывающийся список запросов слияния Редактор Power Query с выделенным элементом

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

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

  3. Выберите State (Состояние ) в таблице Вывод данных на пенсию , а затем выберите запрос State Codes (Коды штатов ).

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

    Снимок экрана: диалоговое окно слияния Редактор Power Query.

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

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

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

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

    Снимок экрана: диалоговое окно

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

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

    Примечание

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

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

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

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

    Снимок экрана: кнопка

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

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