Динамическое отключение нескольких столбцов в LuckyTemplates

В этом руководстве мы вернемся к рассмотренной ранее теме о том, как развернуть несколько столбцов в Power Query с помощью List.Zip. Участник LuckyTemplates спросил на , как сделать этот метод динамическим. Вы можете посмотреть полное видео этого урока в нижней части этого блога.

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

С учетом сказанного давайте перейдем к Power Query.

Оглавление

• Данные в табличном формате
• Изменение кода для динамического разведения нескольких столбцов
• Динамическое отключение нескольких столбцов: добавление пользовательской логики столбцов
• Заключение

Данные в табличном формате

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

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

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

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

Давайте посмотрим на весь код. Здесь нас интересует функция List.Zip . Кратко, он принимает один список в качестве параметра , а затем возвращает список. В результате мы видим, что этот единственный список в данном экземпляре содержит три отдельных списка.

Если мы рассмотрим первый список, то увидим, что он содержит жестко закодированные имена атрибутов, просто текстовые значения, которые были введены. Второй список содержит жестко закодированные ссылки на столбцы для значений часов. Окончательный список содержит жестко закодированные ссылки на столбцы для значений контактов.

Затем List.Zip берет элемент из каждого из этих трех списков и объединяет их в соответствии с их положением в новом списке. Например, каждый первый элемент из этих списков объединяется в новый список. То же самое происходит для всех значений, перечисленных вторыми в этом списке, и так далее.

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

Изменение кода для динамического разведения нескольких столбцов

Так как же нам сделать это динамичным?

Первое, что я делаю, — это просматриваю код, чтобы посмотреть, есть ли место, где мы можем подобрать значения этих атрибутов, не создавая для этого отдельный шаг. Итак, мы собираемся вернуться назад по коду, и этот шаг RemoveNull идеален.

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

Поэтому щелкните правой кнопкой мыши этот заголовок и внизу выберите «Добавить как новый запрос» .

Мы получаем этот список, и это хорошая новость, потому что мы хотим заменить этот жестко закодированный список, который мы видели ранее, динамическим. Все, что нам нужно сделать, это привести значения в порядок.

Давайте начнем с удаления этих нулей. Итак, внутри строки формул мы добавим List.RemoveNulls . Как видите, все элементы повторяются, и это из-за пар значений. Он указан один раз для часов.

И опять же, для контактов, чтобы удалить эти дубликаты, мы можем добавить List.Distinct . Теперь мы создали эту логику, и все, что нам нужно сделать, это скопировать ее и вставить обратно в наш образец запроса.

В примере запроса откройте расширенный редактор и создайте новое имя переменной. Назовем его AttributeList . Затем вставляем код и ставим запятую.

Теперь давайте вернемся к нашему пользовательскому шагу. Внутри List.Zip мы можем заменить этот первый список именем нашей переменной (AttributeList).

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

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

Динамическое отключение нескольких столбцов: добавление пользовательской логики столбцов

Это просто временный столбец, поэтому нам не нужно давать ему правильное имя. Мы удалим его, как только закончим.

Чтобы изменить значения в списке, мы можем использовать функцию List.Transform . Нам нужно преобразовать значения в нашем AttributeList , поэтому я собираюсь вставить это.

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

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

Под страницей мы видим предварительный просмотр этого списка, который мы создали. Итак, вы видите, что заголовки этих столбцов теперь имеют правильный регистр, чтобы они соответствовали заголовкам столбцов, которые мы видим здесь в этом списке.

Теперь у нас есть средства для идентификации полей из нашей записи, к которым мы хотим получить доступ. Итак, давайте снова откроем диалоговое окно нашего пользовательского столбца и добавим Record.SelectFields . Он хочет запись как запись, поэтому мы можем использовать это подчеркивание для доступа к текущей записи, в которой мы находимся. И затем мы дали ему список со всеми этими именами полей, поэтому добавьте закрывающую скобку и нажмите OK .

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

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

Теперь мы снова получаем список, а не запись. Мы снова щелкаем сбоку в пустом пространстве, и вы видите, что теперь у нас есть список только с этими значениями. Так что выглядит хорошо.

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

В нашей строке формул мы выбираем наш второй список и заменяем его нашей логикой.

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

Тогда вместо часов давайте изменим это на контакты .

Ошибок нет и выглядит хорошо. Теперь это динамическое решение для разведения нескольких столбцов.

LuckyTemplates Unpivot Columns — Учебное пособие по Power Query.
Как развернуть столбцы
в LuckyTemplates.

Заключение

Вот как вы динамически разворачиваете несколько столбцов в с помощью List.Zip.

Если появится новый атрибут, он будет автоматически выбран AttributeList. И когда элемент больше не существует, он не будет присутствовать в этом списке.

Я надеюсь, вам понравилось это. Посмотрите полное видео ниже, чтобы получить дополнительные сведения об этом руководстве, и ознакомьтесь с приведенными ниже ссылками, чтобы получить дополнительные сведения о разведении нескольких столбцов в LuckyTemplates.

Всего наилучшего

Мелисса