Формирование исходных данных
Редактор Power Query в Power BI Desktop позволяет формировать (преобразовывать) импортированные данные. Вы можете выполнять различные операции, например переименование столбцов или таблиц, изменение текста на числа, удаление строк, установка первой строки в качестве заголовков и многое другое. Важно сформировать данные, чтобы убедиться, что они соответствуют вашим потребностям и подходят для использования в отчетах.
Вы загрузили необработанные данные о продажах из двух источников в модель Power BI. Некоторые данные поступили из CSV-файла, созданного вручную отделом продаж в Microsoft Excel. Другие данные были загружены через подключение к системе планирования корпоративных ресурсов (ERP) вашей организации. Вы изучаете данные в Power BI Desktop и замечаете, что они находятся в беспорядке — некоторые данные не нужны, некоторые необходимые данные имеют неправильный формат.
Перед началом создания отчетов необходимо использовать редактор Power Query, чтобы выполнить очистку и формирование этих данных.
Начало работы с редактором Power Query
Чтобы приступить к формированию данных, откройте редактор Power Query, выбрав вариант Преобразовать данные на вкладке Главная в Power BI Desktop.
В редакторе Power Query данные в выбранном запросе отображаются в середине экрана, а в левой части окна на панели Запросы приводится список доступных запросов (таблиц).
При работе в редакторе Power Query записываются все шаги, выполняемые для формирования данных. Затем каждый раз, когда запрос подключается к источнику данных, он автоматически применяет эти шаги, поэтому данные формируются так, как вы настроили. Редактор Power Query вносит изменения только в конкретное представление данных, поэтому вы можете быть уверены в изменениях, которые вносятся в исходный источник данных. Список шагов можно просмотреть в правой части экрана, на панели Параметры запроса вместе со свойствами запроса.
Лента редактора Power Query содержит множество кнопок, которые можно использовать для выбора, просмотра и формирования данных.
Дополнительные сведения о доступных функциях и возможностях см. в разделеЛента запросов.
Примечание
В редакторе Power Query контекстные меню и вкладка Преобразовать на ленте позволяют выбрать многие из этих же параметров.
Указание заголовков и имен столбцов
Первым шагом в формировании исходных данных является обнаружение заголовков и имен столбцов в данных, а затем оценка их расположения, чтобы убедиться, что они находятся в нужном месте.
На следующем снимке экрана исходные данные в CSV-файле SalesTarget (пример не предоставлен) содержат сгруппированные по продуктам целевые показатели и разделенные по месяцам подкатегории, упорядоченные в столбцы.
Однако вы заметили, что данные не были импортированы должным образом.
Следовательно, данные трудно читать. С данными в текущем состоянии возникла проблема, так как заголовки столбцов находятся в разных строках (отмечены красным цветом), а у нескольких столбцов непонятные имена, например Column1,Column2 и т. д.
Определив расположение заголовков и имен столбцов, можно внести изменения для реорганизации данных.
Продвижение заголовков
При создании таблицы в Power BI Desktop в редакторе Power Query предполагается, что все данные принадлежат к строкам таблицы. Однако источник данных может иметь первую строку, содержащую имена столбцов, как это было в предыдущем примере SalesTarget. Чтобы устранить эту неточность, необходимо повысить уровень первой строки таблицы до заголовков столбцов.
Можно повысить уровень заголовков двумя способами: выбрав Использовать первую строку в качестве заголовков на вкладке Главная или нажав кнопку раскрывающегося списка рядом с ячейкой Column1 и выбрав Использовать первую строку в качестве заголовков.
На следующем рисунке показано, как функция Использовать первую строку в качестве заголовков влияет на данные:
Переименование столбцов
Следующим шагом в формировании данных является проверка заголовков столбцов. Можно обнаружить, что один или несколько столбцов имеют неправильные заголовки, заголовок содержит ошибку правописания, не соответствует соглашению об именовании заголовков или непонятен для пользователя.
См. предыдущий снимок экрана, на котором показано влияние функции Использовать первую строку в качестве заголовков. Обратите внимание, что столбец, содержащий подкатегорию Name, теперь имеет заголовок столбца Month. Заголовок столбца неверен, поэтому его необходимо переименовать.
Заголовки столбцов можно переименовывать двумя способами. Один из подходов — щелкнуть правой кнопкой мыши заголовок, выбратьПереименовать, изменить имя, а затем нажать клавишу ВВОД. Или можно дважды щелкнуть заголовок столбца и заменить его имя на правильное.
Эту ошибку также можно обойти, удалив (пропустив) первые две строки, а затем переименовав столбцы.
Удаление верхних строк
При формировании данных может потребоваться удалить некоторые верхние строки, например, если они пустые или содержат данные, которые не нужны в отчетах.
Продолжая работу с примером SalesTarget, обратите внимание, что первая строка пуста (не содержит данных), а вторая строка содержит данные, которые больше не требуются.
Чтобы удалить лишние строки, выберите Удалить строки>Удалить верхние строки на вкладке Главная.
Удаление столбцов
Ключевым шагом в процессе формирования данных является удаление ненужных столбцов. Рекомендуется удалить столбцы как можно раньше. Один из способов удалить столбец — ограничить его при получении данных из источника данных. Например, при извлечении данных из реляционной базы данных с помощью SQL необходимо ограничить столбец, извлекаемый с помощью списка столбцов в инструкции SELECT.
Рекомендуется удалять столбцы на раннем этапе процесса, особенно если вы установили связи между таблицами. Удаление ненужных столбцов поможет сосредоточиться на необходимых данных и повысить общую производительность Power BI Desktop семантических моделей и отчетов.
Изучите каждый столбец и подумайте, действительно ли вам нужны содержащиеся в нем данные. Если вы не планируете использовать эти данные в отчете, столбец не добавляет значения семантической модели. и его следует удалить. Вы всегда можете добавить столбец позже, если ваши требования со временем изменятся.
Удалить столбцы можно двумя способами. Первый способ — выбрать столбцы, которые необходимо удалить, а затем на вкладке Главная нажать Удалить столбцы.
Кроме того, можно выбрать столбцы, которые необходимо сохранить, а затем на вкладке Главная выбрать Удалить столбцы>Удалить другие столбцы.
Отмена свертывания столбцов
Отмена свертывания — это полезная функция Power BI. Эту функцию можно использовать с данными из любого источника, но чаще всего она используется при импорте данных из Excel. В следующем примере показан документ Excel с данными о продажах.
Хотя данные могут быть изначально понятны, было бы сложно рассчитать общий объем продаж за 2018 и 2019 годы. Вам следует использовать эти данные в Power BI с тремя столбцами: Month, Year и SalesAmount.
При импорте данных в Power Query они будут выглядеть как на следующем рисунке.
Затем переименуйте первый столбец в Month. Этот столбец неверно помечен, так как в Excel заголовок относился к столбцам 2018 и 2019. Выделите столбцы 2018 и 2019, выберите вкладку Преобразовать в Power Query, а затем выберите Отменить свертывание.
Вы можете переименовать столбец Attribute в Year, а столбец Value — в SalesAmount.
Отмена свертывания упрощает процесс создания мер DAX в данных. Выполнив этот процесс, вы получили более простой способ создания срезов данных по столбцам Year и Month.
Сводные столбцы.
Если вы формируете неструктурированные данные (то есть подробные, но не упорядоченные и не сгруппированные), отсутствие структуры затрудняет выявление закономерностей в данных.
Для преобразования неструктурированных данных в таблицу, содержащую статистическое значение для каждого уникального значения в столбце, можно использовать функцию Сводный столбец. Например, эту функцию можно использовать для суммирования данных с помощью различных математических функций, таких как Count, Minimum, Maximum, Median, Average или Sum.
В примере SalesTarget можно выполнить сведение столбцов, чтобы получить количество подкатегорий продуктов в каждой категории продуктов.
На вкладке Преобразование выберите Преобразовать > Сводные столбцы.
В открывшемся окне Сводный столбец выберите столбец из списка Столбцы значений, например Имя подкатегории. Разверните дополнительные параметры и выберите параметр из списка Функция агрегированного значения, например Count (All), и нажмите ОК.
На следующем рисунке показано, как функция Сводный столбец изменяет способ организации данных.
В редакторе Power Query записываются все шаги, которые необходимо выполнить для формирования данных, а список шагов отображается на панели Параметры запроса. Если вы внесли все необходимые изменения, выберите Закрыть & Применить, чтобы закрыть Редактор Power Query и применить изменения к семантической модели. Однако перед нажатием кнопки Закрыть & Применить можно выполнить дальнейшие действия по очистке и преобразованию данных в Редактор Power Query. Эти действия описаны далее в этом модуле.