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

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

Из этого руководства вы узнаете, как выполнять следующие задачи:

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

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

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

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

Редактор Power Query записывает указанные действия (например, переименовать таблицу, преобразовать тип данных или удалить столбец). Каждый раз, когда этот запрос подключается к источнику данных, Редактор Power Query выполняет эти действия, чтобы данные всегда формировались таким образом, как вы указали. Этот процесс происходит всякий раз, когда вы используете Редактор Power Query или для тех, кто использует общий запрос, например в служба Power BI. Эти действия записываются последовательно в области параметров запроса в разделе "ПРИМЕНЕННЫЕ ШАГИ". Мы рассмотрим каждый из этих шагов, описанных в этой статье.

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

   

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

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

   

3. В диалоговом окне "Навигатор" установите флажок для записи, которая начинается сIndividual factor scores, а затем выберите "Преобразовать данные".

   

   Совет

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

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

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

   

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

   

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

7. На ленте "Добавить столбец" выберите "Настраиваемый столбец".

   

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

   ( [Quality of life] + [Cost of housing] + [Public health] + [Crime] + [Taxes] + [Weather] + [#"Non-housing cost of living"] ) / 7
   

9. Убедитесь, что сообщение о состоянии не обнаружено синтаксической ошибки, а затем нажмите кнопку "ОК".

   

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

    

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

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

• Измените рейтинг, удалив столбец.

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

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

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

• Сортировка данных.

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

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

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

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

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

   

   Примечание.

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

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

   

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

   

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

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

   

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

   

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

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

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

      

   2. Выберите столбец "Создать оценку", а затем отобразите формулу данных столбца, включив флажок "Панель формул" на вкладке "Вид".

      

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

      = Table.AddColumn(#"Removed Columns", "New score", each ( [Quality of life] + [Cost of housing] + [Public health] + [Crime] + [Taxes] + [#"Non-housing cost of living"] ) / 6)
      

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

   Примечание.

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

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

   

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

7. В ИНСТРУКЦИИ APPLIED выберите шаг, предшествующий пользовательскому столбцу, который является шагом "Удаленные столбцы ". Здесь мы заменим значение рейтинга стоимости жилья в Орегоне. Щелкните правой кнопкой мыши соответствующую ячейку, содержащую стоимость жилья орегона, а затем выберите "Заменить значения". Обратите внимание, какой примененный шаг в данный момент выбран.

   

8. Выберите Вставить.

   Так как мы вставим шаг, Редактор 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_and_territory_abbreviationsа затем нажмите кнопку "ОК".

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

   

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

   Совет

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

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

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

   

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

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

   

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

   

   Примечание.

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

   Примечание.

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

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

   

5. Переименуйте столбцы в имя состояния и код состояния. Чтобы переименовать таблицу, введите коды состояний в поле "Имя" в области параметров запроса.

   

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

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

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

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

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

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

2. Выберите "Запросы > слияния" на вкладке "Главная" на ленте.

   

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

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

3. Выберите состояние из таблицы данных о выходе на пенсию, а затем выберите запрос кодов состояния.

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

   

4. Нажмите ОК.

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

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

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

   

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

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

   Примечание.

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

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

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

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

Связанный контент

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

• Что такое Power BI Desktop?
• Обзор запросов в Power BI Desktop
• Data sources in Power BI Desktop (Источники данных в Power BI Desktop)
• Подключение к источникам данных в Power BI Desktop
• Выполнение распространенных задач запросов в Power BI Desktop