https://www.myonlinetraininghub.com/extracting-data-from-lists-and-records-in-power-query
Автор оригинала: Philip Treacy
У вас могут быть данные, которые содержатся в списках или записях, или даже списки внутри записей, или записи внутри списков. Я видел вложенные данные, подобные этому, поступающие из API или Sharepoint среди других источников.
В таких случаях вы можете захотеть извлечь только определенные фрагменты данных из списка (ов) или записи (ов), и в этом сообщении в блоге показано, как это сделать.
Смотрите видео:
Скачать пример рабочей книги Excel:
Это работает в Power BI и в Excel!
Показанный здесь M-код можно использовать в Power Query как в Power BI, так и в Excel.
Записи в списках
Я начинаю с таблицы, подобной этой, в Power Query.
Первый столбец содержит названия мест, где в моем бизнесе реализуются текущие проекты. Вторая колонка предназначена для отображения имени руководителя проекта для каждого местоположения.
Как вы можете видеть на данный момент, некоторые строки во втором столбце содержат списки, а некоторые строки пусты.
В других ситуациях вы, возможно, захотите отфильтровать пустые строки, но в данном случае я хочу сохранить все строки. Я хочу знать, в каком месте нет менеджера проекта.
Просматривая содержимое списков (щелкните рядом со словом List), я вижу, что первый список содержит запись:
второй список пуст:
и третий список (в строке 4) также содержит запись.
Нужные мне данные находятся в записях, поэтому мне нужно проверить эти записи, чтобы увидеть, как они структурированы. Во-первых, давайте продублируем запрос, чтобы я не вносил никаких непреднамеренных изменений в свой основной запрос. Щелкните правой кнопкой мыши на запросе, а затем выберите Duplicate:
В дублированном запросе щелкните по слову List в строке 1 столбца Data, что дает мне следующее:
Щелчок рядом со словом Record позволяет просмотреть данные в записи, которые являются следующими
Запись содержит 3 поля: ID, Name и Email. Я хочу узнать Name.
Возвращаясь к основному запросу, мне нужно добавить новый пользовательский столбец: с ленты -> Add Column -> Custom Column
Что мне нужно сделать, так это получить доступ к записи из списка в столбце данных. Я могу сделать это, просто сославшись на элемент (ы) в списках, используя индексные номера (например, массив).
Списки индексируются с 0, и эти списки содержат только 1 элемент, поэтому для доступа к записи в позиции 0 я использую этот код
Это восстановит запись, но мне нужно получить доступ к данным в этой записи. Для этого я использую функцию Record.Field.
Record.Field принимает 2 параметра: запись и название поля, из которого будут получены данные. Я хочу получить имя менеджера проекта из поля Name, поэтому мой пользовательский код столбца теперь выглядит следующим образом
Если я нажму "ОК", чтобы создать новый столбец, я получу это
Ошибки в строках 3 и 5 связаны с тем, что я пытаюсь получить данные из списков и записей, которые не существуют в соответствующей строке столбца Data. Ошибка в строке 2 связана с тем, что этот список пуст.
Исправить это несложно. Отредактируйте шаг, на котором был создан пользовательский столбец (нажмите на шестеренку рядом с названием шага).
Затем оберните код в try .. otherwise.
Использование try .. otherwise перехватывает любые ошибки, сгенерированные кодом, и позволяет мне указать пустую строку по умолчанию "" в качестве результата при возникновении ошибки.
Списки в записях
Я начинаю с этой таблицы, где столбец данных содержит записи, и в этих записях находятся списки. Как и в первом примере, список внутри записи для Брисбена пуст.
Список можно извлечь из записи, добавив новый пользовательский столбец и используя этот код
Который дает этот новый столбец под названием List Data. Просматривая список в строке 1, я вижу, что в списке есть 3 элемента.
Чтобы извлечь имя менеджера проекта, я могу использовать индекс для этого элемента, который равен 1, поскольку списки индексируются с 0. Я могу создать новый пользовательский столбец с помощью этого кода
В результате получается эта таблица
Однако эти два шага можно объединить в один. Взяв код, используемый для извлечения списка из записи, добавьте {1} после вызова Record.Field для доступа к имени руководителя проекта.
Списки и записи
Если у вас есть данные в столбце, который представляет собой комбинацию списков и записей
и данные внутри списков и записей структурированы так, как в моих первых двух примерах
Тогда я смогу использовать функцию Value.Is для проверки того, есть ли в строке список или запись, а затем извлечения данных соответствующим образом.
Чтобы использовать Value.Is, вы передаете в неё структуру данных / значение, которые вы хотите протестировать, и тип, который вы хотите проверить. Итак, давайте добавим новый пользовательский столбец и проверим наличие списков с помощью этого кода, и если значение в столбце данных является списком, извлеките имя из позиции 1 в этом списке
Чтобы проверить наличие записей, я могу добавить предложение else if, и если обнаружена запись, используйте Record.Field для получения имени руководителя проекта из поля Name.
Приведем эту таблицу с именами, извлеченными как из списков, так и из записей.
Если ни список, ни запись не найдены, то создается пустая строка. Я не использовал try .. otherwise здесь, поскольку я знаю, что структуры списков и записей не вызовут ошибку, но вы можете использовать try .. otherwise, если вы думаете, что можете получить ошибки.