Динамічно скасуйте зведення кількох стовпців у LuckyTemplates

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

Я назвав це розширеним, тому що ми будемо писати кілька функцій M, і деякі з них можуть бути навіть новими для вас.

З огляду на це, давайте перейдемо до Power Query.

Зміст

• Дані в табличному форматі
• Зміна коду для динамічного відведення кількох стовпців
• Динамічне скасування зведення кількох стовпців: додавання спеціальної логіки стовпців
• Висновок

Дані в табличному форматі

Як ви бачите, ці дані вже перетворено в правильний формат планшета, придатний для аналізу. Цей процес, хоч і дуже важливий, не буде описано тут, але ми коротко розглянемо кожен крок.

На кроці джерела ми бачимо постійне повторення пар значень, якими є години та контакти для кожного з перелічених вище атрибутів.

У Застосованих кроках ви можете побачити решту кроків. Ми видалили деякі верхні рядки, видалили порожні стовпці, очистили наші текстові значення та заповнили їх. Потім ми транспонували наші дані, видалили порожній запис, об’єднали стовпці один і два, знову очистили наші текстові значення та транспонували наші дані назад.

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

Давайте розглянемо весь код. Нас тут цікавить функція List.Zip . Короткий підсумок: він приймає один список як a, а потім повертає список. У результаті ми бачимо, що цей єдиний список у цьому випадку містить три окремих списки.

Якщо ми перевіримо перший список, він містить жорстко закодовані назви атрибутів, лише текстові значення, які було введено. Другий список містить жорстко закодовані посилання на стовпці для значень годин. Остаточний список містить жорстко закодовані посилання на стовпці для значень контактів.

Потім List.Zip бере елемент з кожного з цих трьох списків і об’єднує їх на основі їхньої позиції в новому списку. Наприклад, кожен перший елемент із цих списків об’єднується в новий список. Те ж саме відбувається для всіх значень, перелічених другим у цьому списку, і так далі.

Жорстко закодовані значення можуть викликати стурбованість, якщо атрибути навряд чи зміняться з часом. Отже, якщо наступного разу не всі предмети будуть присутні або з’являться нові предмети, ви можете потрапити в біду.

Зміна коду для динамічного відведення кількох стовпців

Отже, як нам зробити цю динаміку?

Перше, що я роблю, це проходжу через код, щоб побачити, чи є місце, де ми можемо підібрати ці значення атрибутів без необхідності розробляти окремий крок для цього. Отже, ми збираємося повернутися до коду, і цей крок RemoveNull ідеальний.

Ви бачите, що всі значення атрибутів перераховані тут у першій колонці. Ми можемо використовувати інтерфейс користувача, щоб витягнути їх.

Тож клацніть правою кнопкою миші цей заголовок і внизу виберіть Додати як новий запит .

Ми отримали цей список, і це гарна новина, тому що ми хочемо замінити жорстко закодований список, який ми бачили раніше, динамічним. Все, що нам потрібно зробити, це привести в порядок цінності.

Почнемо з видалення цих нулів. Тож у рядок формул ми додамо List.RemoveNulls . Як ви можете бачити зараз, усі елементи повторюються, і це через пари значень. Він вказується один раз для годин.

Знову ж таки, для контактів, щоб видалити ці дублікати, ми можемо додати List.Distinct . Тепер ми створили цю логіку, і все, що нам потрібно зробити, це скопіювати її та вставити назад у наш зразок запиту.

У прикладі запиту відкрийте розширений редактор і створіть нову назву змінної. Назвемо це AttributeList . Потім вставляємо код і ставимо кому.

Тепер повернемося до нашого користувацького кроку. У List.Zip ми можемо замінити перший список назвою нашої змінної (AttributeList).

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

Нам просто потрібно додати суфікс до кожного елемента. Отже, давайте повернемося на крок назад і додамо спеціальний стовпець, щоб створити деяку логіку.

Динамічне скасування зведення кількох стовпців: додавання спеціальної логіки стовпців

Це лише тимчасова колонка, тому нам не потрібно давати їй правильну назву. Ми видалимо його, коли закінчимо.

Щоб змінити значення в списку, можна скористатися функцією List.Transform . Нам потрібно перетворити значення в нашому AttributeList , тому я збираюся вставити це.

Тут, у заголовках, ми бачимо, що текстові значення починаються з великої літери, а решта – у нижньому регістрі. Наш атрибут містить лише слово з великої літери, тому ми також маємо трансформувати це текстове значення. І ми можемо використовувати підкреслення для доступу до кожного елемента списку, і ми додамо цей суфікс.

Таким чином, ми отримуємо значення списку в нашому стовпці. Неважливо, який з них ви виберете, просто клацніть збоку в білому просторі будь-якого з них тут.

Під сторінкою ми бачимо попередній перегляд списку, який ми створили. Отже, ви бачите, що ми маємо заголовки стовпців, які тепер мають правильний регістр, що відповідає заголовкам стовпців, які ми бачимо тут із цим списком.

Тепер у нас є засоби для ідентифікації полів із нашого запису, до яких ми хочемо отримати доступ. Отже, давайте знову відкриємо діалогове вікно спеціального стовпця та додамо Record.SelectFields . Він хоче запис як запис, тому ми можемо використовувати це підкреслення для доступу до поточного запису, який ми використовуємо. А потім ми надали йому список із усіма цими назвами полів, тож додайте закриваючу дужку та натисніть OK .

Тож у нас більше немає списку, але тепер ми отримуємо запис. Давайте ще раз клацнемо збоку в білому просторі, щоб переглянути вміст цього запису. Тут ми бачимо, що в ньому перераховано назви полів запису та значення полів запису.

Але нас цікавлять лише значення полів запису, тому нам потрібно їх витягти. Давайте повернемося до діалогового вікна спеціального стовпця та додамо Record.FieldsValues .

Тепер ми знову отримуємо список, а не запис. Ми знову клацаємо збоку в пробілі, і ви бачите, що тепер у нас є список лише з цими значеннями. Так що виглядає добре.

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

На панелі формул ми вибираємо другий список і замінюємо його нашою логікою.

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

Тоді замість годин давайте змінимо це на контакти .

Немає помилок, виглядає добре. Тепер це динамічне рішення для відключення кількох стовпців.

Висновок

Ось як ви динамічно скасовуєте кілька стовпцівза допомогою List.Zip.

Якщо з’явиться новий атрибут, він автоматично буде підібраний AttributeList. І коли елемент більше не існує, він не буде присутній у цьому списку.

Сподіваюся, вам сподобався цей. Перегляньте повне відео нижче, щоб дізнатися більше про цей навчальний посібник, і перегляньте посилання нижче, щоб дізнатися більше про те, як скасувати зведення кількох стовпців у LuckyTemplates.

Все найкраще

Меліса