Руководство. Формирование и объединение данных в 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. Эти шаги записываются последовательно в области "Запрос Параметры" в разделе "ПРИМЕНЕННЫЕ ШАГИ". Мы рассмотрим каждый из этих шагов, описанных в этой статье.
Импортируйте данные из веб-источника. Выберите раскрывающийся список "Получить данные", а затем выберите "Веб".
Вставьте этот URL-адрес в диалоговое окно "Из Интернета " и нажмите кнопку "ОК".
https://www.fool.com/research/best-states-to-retire
В диалоговом окне "Навигатор" выберите
Table 1пункт "Преобразовать данные".
Совет
Некоторые сведения в таблицах из предыдущего URL-адреса могут изменяться или обновляться иногда. В результате может потребоваться соответствующим образом настроить выбранные варианты или шаги, описанные в этой статье.
Откроется окно Редактор запросов Power BI. Вы можете просмотреть шаги по умолчанию, примененные до сих пор, в области "Запрос Параметры" в разделе "ПРИМЕНЕННЫЕ ШАГИ".
- Источник: Подключение на веб-сайт.
- Извлеченная таблица из Html: выбор таблицы.
- Продвигаемые заголовки: изменение верхней строки данных в заголовки столбцов.
- Измененный тип: изменение типов столбцов, импортируемых в виде текста, на их выводимые типы.
Измените имя таблицы по умолчанию
Table 1Retirement Dataна , а затем нажмите клавишу ВВОД.
Существующие данные упорядочивается по весовой оценке, как описано на исходной веб-странице в разделе "Методология". Давайте добавим пользовательский столбец, чтобы вычислить другую оценку. Затем мы сортируем таблицу в этом столбце, чтобы сравнить рейтинг пользовательской оценки с существующим рангом.
На ленте "Добавить столбец" выберите "Настраиваемый столбец".
В диалоговом окне "Пользовательский столбец" введите "Создать оценку". В формуле настраиваемого столбца введите следующие данные:
( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] + [Weather] ) / 8Убедитесь, что сообщение о состоянии не обнаружено синтаксической ошибки и нажмите кнопку "ОК".
В Параметры запроса список ПРИМЕНЕННЫХ ШАГОВ теперь отображает новый добавленный настраиваемый шаг, который мы только что определили.
Настройка данных
Прежде чем работать с этим запросом, давайте внесите несколько изменений, чтобы изменить свои данные:
Измените рейтинг, удалив столбец.
Например, предположим , что погода не является фактором в наших результатах. Удаление этого столбца из запроса не влияет на другие данные.
Исправьте все ошибки.
Так как мы удалили столбец, нам нужно настроить расчеты в столбце "Новая оценка ", изменив ее формулу.
Сортировка данных.
Сортируйте данные на основе столбца "Новая оценка " и сравнивайте с существующим столбцом ранга .
Замените данные.
Мы рассмотрим, как заменить определенное значение и как вставить примененный шаг.
Эти изменения описаны в следующих шагах.
Чтобы удалить столбец "Погода ", выберите столбец на вкладке "Главная " на ленте и нажмите кнопку " Удалить столбцы".
Примечание.
Новые значения оценки не изменились из-за упорядочения шагов. Редактор Power Query записывает шаги последовательно, но независимо друг от друга. Чтобы применить действия в другой последовательности, можно переместить каждый примененный шаг вверх или вниз.
Щелкните правой кнопкой мыши шаг, чтобы просмотреть его контекстное меню.
Перейдите к последнему шагу, удаляя столбцы, до того, как раз выше добавленного настраиваемого шага.
Выберите добавленный пользовательский шаг.
Обратите внимание, что столбец "Новая оценка " теперь отображает ошибку , а не вычисляемое значение.
Существует несколько способов получить дополнительные сведения о каждой ошибке. Если выбрать ячейку без нажатия слова Error, Редактор Power Query отображает сведения об ошибке.
Если выбрать слово "Ошибка" напрямую, Редактор Power Query создает примененный шаг в области "Запрос Параметры" и отображает сведения об ошибке. Так как нам не нужно отображать сведения об ошибке в другом месте, нажмите кнопку "Отмена".
Чтобы устранить ошибки, необходимо изменить два изменения, удалив имя столбца Погоды и изменив разделитель с 8 до 7. Эти изменения можно внести двумя способами:
Щелкните правой кнопкой мыши шаг "Настраиваемый столбец" и выберите пункт "Изменить Параметры". Откроется диалоговое окно "Настраиваемый столбец" , используемое для создания столбца "Новая оценка ". Измените формулу, как описано ранее, пока она не будет выглядеть следующим образом:
Выберите столбец "Создать оценку", а затем отобразите формулу данных столбца, включив строку формул проверка box на вкладке "Вид".
Измените формулу, как описано ранее, пока она не будет выглядеть так, а затем нажмите клавишу ВВОД.
= 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 заменяет данные измененными значениями и Добавленный настраиваемый шаг завершается без ошибок.
Примечание.
Вы также можете выбрать команду "Удалить ошибки" с помощью ленты или меню правой кнопкой мыши, которое удаляет все строки с ошибками. Однако в этом руководстве мы хотим сохранить все данные в таблице.
Сортируйте данные на основе столбца "Новая оценка ". Во-первых, выберите последний примененный шаг, добавив custom , чтобы отобразить последние данные. Затем выберите раскрывающийся список рядом с заголовком столбца "Создать оценку " и выберите "Сортировка по убыванию".
Теперь данные отсортированы в соответствии с новой оценкой. Вы можете выбрать примененный шаг в любом месте списка и продолжить формирование данных в этой последовательности. Редактор Power Query автоматически вставляет новый шаг непосредственно после выбранного в данный момент примененного шага.
В ИНСТРУКЦИИ APPLIED выберите шаг, предшествующий пользовательскому столбцу, который является шагом "Удаленные столбцы ". Здесь мы заменим значение рейтинга стоимости жилья в Орегоне. Щелкните правой кнопкой мыши соответствующую ячейку, содержащую значение стоимости жилья Орегона, а затем выберите "Заменить значения". Обратите внимание, какой примененный шаг в данный момент выбран.
Выберите Вставить.
Так как мы вставим шаг, Редактор Power Query напоминает нам, что последующие шаги могут привести к разрыву запроса.
Измените значение данных на 100,0.
Редактор Power Query заменяет данные для Орегона. При создании нового примененного шага Редактор Power Query именуйте его на основе действия, в данном случае замененное значение. Если в запросе есть несколько шагов с одинаковым именем, Редактор Power Query добавляет большее число к имени каждого последующего примененного шага.
Выберите последний примененный шаг, отсортированные строки.
Обратите внимание, что данные изменились в отношении нового рейтинга Орегона. Это изменение происходит из-за вставки шага "Замененное значение " в правильном расположении перед добавлением настраиваемого шага.
Теперь мы сформировали наши данные в той степени, в которой мы должны. Затем давайте подключимся к другому источнику данных и объединим данные.
Объединение данных
Данные о различных состояниях интересны и будут полезны для создания дальнейших усилий анализа и запросов. Однако большинство данных о штатах использует двухбуквенное сокращение для кодов состояний, а не полное имя состояния. Нам нужен способ связать имена состояний с их сокращенными именами.
Существует еще один общедоступный источник данных, который обеспечивает эту связь, но он нуждается в справедливом количестве форм, прежде чем мы можем подключить его к нашей пенсионной таблице. Чтобы сформировать данные, выполните следующие действия.
На ленте "Главная" в Редактор Power Query выберите новый исходный > веб-сайт.
Введите адрес веб-сайта для сокращенных состояний, https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviationsа затем выберите Подключение.
Навигатор отображает содержимое веб-сайта.
Выбор кодов и сокращений для штатов США, федерального округа, территорий и других регионов.
Совет
Чтобы проанализировать данные этой таблицы до нужного, потребуется немного. Существует ли более быстрый или простой способ выполнить следующие действия? Да, можно создать связь между двумя таблицами и сформировать данные на основе этой связи. В следующем примере показано, как работать с таблицами. Однако связи помогают быстро использовать данные из нескольких таблиц.
Чтобы получить данные в форму, выполните следующие действия.
Удалите верхнюю строку. Так как это результат создания таблицы веб-страницы, нам не нужно. На ленте "Главная" выберите "Удалить строки>", чтобы удалить верхние строки.
Откроется диалоговое окно "Удалить верхние строки". Укажите 1 строку для удаления.
Продвигайте новую верхнюю строку к заголовкам с использованием заголовков первой строки в качестве заголовков на вкладке "Главная " или на вкладке "Преобразование " на ленте.
Поскольку таблица данных о выходе на пенсию не содержит сведений о Вашингтоне или территориях, нам нужно отфильтровать их из нашего списка. Выберите имя и состояние раскрывающегося списка столбца region_1, а затем снимите все проверка boxes, кроме состояния.
Удалите все ненужные столбцы. Так как нам нужно только сопоставление каждого состояния с официальным двухбуквенный сокращением (имя и состояние столбцов региона и ANSI ), мы можем удалить другие столбцы. Сначала выберите имя и состояние столбца региона , а затем удерживайте клавишу CTRL и выберите столбец ANSI . На вкладке "Главная " на ленте выберите "Удалить столбцы > ", чтобы удалить другие столбцы.
Примечание.
Последовательность примененных шагов в Редактор Power Query важна и влияет на формирование данных. Также важно рассмотреть, как один шаг может повлиять на другой последующий шаг. Например, если удалить шаг из примененных шагов, последующие шаги могут не вести себя так, как изначально.
Примечание.
При изменении размера окна Редактор Power Query, чтобы уменьшить ширину, некоторые элементы ленты будут сокращены, чтобы лучше всего использовать видимое пространство. При увеличении ширины окна Редактор Power Query элементы ленты расширяются, чтобы сделать большую часть увеличенной области ленты.
Переименуйте столбцы и таблицу. Существует несколько способов переименовать столбец: сначала выберите столбец, а затем выберите "Переименовать " на вкладке "Преобразование " на ленте или щелкните правой кнопкой мыши и выберите " Переименовать". На следующем рисунке показаны оба варианта, но вам нужно выбрать только один.
Переименуйте столбцы в имя состояния и код состояния. Чтобы переименовать таблицу, введите коды состояний именв области Параметры запроса.
Объединение запросов
Теперь, когда мы сформировали таблицу кодов состояний так, как мы хотим, давайте объединим эти две таблицы или запросы в одну. Так как теперь таблицы являются результатом запросов, которые мы применили к данным, они часто называются запросами.
Есть два основных способа слияния запросов — объединение и добавление.
- Для одного или нескольких столбцов , которые вы хотите добавить в другой запрос, вы объединяете запросы.
- Для одной или нескольких строк данных, которые вы хотите добавить в существующий запрос, добавьте этот запрос.
В этом случае мы хотим объединить запросы:
В левой области Редактор Power Query выберите запрос, в который нужно объединить другой запрос. В этом случае это данные о выходе на пенсию.
Выберите "Запросы > слияния" на вкладке "Главная" на ленте.
Возможно, вам будет предложено задать уровни конфиденциальности, чтобы убедиться, что данные объединяются без включения или передачи данных, которые вы не хотите передать.
Откроется окно слияния. Он предложит выбрать таблицу, которую вы хотите объединить в выбранную таблицу, и соответствующие столбцы, используемые для слияния.
Выберите состояние из таблицы данных о выходе на пенсию, а затем выберите запрос кодов состояния.
При выборе соответствующих столбцов кнопка "ОК " включена.
Нажмите ОК.
Редактор Power Query создает новый столбец в конце запроса, который содержит содержимое таблицы (запроса), который был объединен с существующим запросом. Все столбцы из объединенного запроса сложены в столбец, но вы можете развернуть таблицу и включить все нужные столбцы.
Чтобы развернуть объединенную таблицу и выбрать, какие столбцы необходимо включить, выберите значок развертывания (
).Откроется окно Развернуть.
В этом случае мы хотим только столбец "Код состояния". Выберите этот столбец, снимите имя исходного столбца в качестве префикса, а затем нажмите кнопку "ОК".
Если мы оставили поле проверка box, выбранное для имени исходного столбца в качестве префикса, объединенный столбец будет называться State Code.State Code.
Примечание.
Если вы хотите изучить, как использовать таблицу кодов состояний, можно поэкспериментировать немного. Если вы не любите результаты, просто удалите этот шаг из списка ПРИМЕНЕННЫХ ШАГОВ в области "Запрос Параметры", и запрос возвращается в состояние перед применением этого шага развертывания. Это можно сделать столько раз, сколько вам нравится, пока процесс развертывания не будет выглядеть так, как это нужно.
Теперь у нас есть один запрос (таблица), который объединяет два источника данных, каждый из которых был сформирован в соответствии с нашими потребностями. Этот запрос может быть основой для интересных подключений к данным, таких как статистика расходов на жилье, качество жизни или уровень преступности в любом штате.
Чтобы применить изменения и закрыть Редактор Power Query, нажмите кнопку "Закрыть" и "Применить" на вкладке "Главная лента".
Преобразованная семантическая модель отображается в Power BI Desktop, которую можно использовать для создания отчетов.
Связанный контент
Дополнительные сведения о Power BI Desktop и его возможностях см. в следующих ресурсах: