Розділити та вилучити значення, розділені комами

На сьогоднішній день, якщо ви прочитаєте мої попередні дописи в серії Остаточний посібник з Unpivot в Excel (усі публікації тут), ви будете впевнені в наступному виклику: Як розділити значення, розділені комами, у Power Query.

У нас є таблиця назв подій, дат та імен учасників, розділених комами, і ми хочемо створити зведену таблицю, яка покаже нам учасників та їх кількість подій (як показано на наступному скріншоті).

У цій публікації ми розглянемо інтуїтивне рішення, яке включає трансформацію поділу та відтворення, але тоді ми побачимо набагато кращий та масштабований спосіб досягнення нашої мети.

Почнемо з покрокового керівництва інтуїтивним рішенням.

Відкрийте цю книгу, виберіть будь-яку комірку в Таблиця1, і натисніть З таблиці в Отримати та перетворити розділ Дані на вкладці в Excel 2016 (Або, якщо ви використовуєте Excel 2010 або 2013, натисніть З таблиці в Power Query після встановлення надбудови).

Відкриється редактор запитів. Клацніть правою кнопкою миші Учасники виберіть стовпець Розділена колона, а потім клацніть Розділювач ...

В Розділена колона за допомогою розділювача діалогове вікно, виберіть Кома як роздільник.

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

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

Ви бачите, що Power Query пропонує розділити Учасники стовпець на 7 стовпців. Чому лише 7? Оскільки редактор запитів визначив у попередньому перегляді максимум 7 значень, розділених комами. На даний момент ви можете здогадатися, що весь набір даних зазвичай перевищує попередній перегляд, тому ми можемо стикатися з випадками, коли у нас більше 7 значень, розділених комами. Добре, клацніть в порядку.

Зараз у нас 7 стовпців: Учасники.1 до Учасники.7, і ми можемо виконати невідворотне перетворення.

Виділіть стовпці Подія і Дата, клацніть правою кнопкою миші на одному з їх заголовків і виберіть Видалити інші стовпці.

Тепер ми можемо видалити стовпець Атрибут і змініть назву стовпця Значення до Учасник. Ми також можемо змінити тип Дата на сьогоднішній день.

Це воно. Ми досягли бажаного формату Подія, Дата і Учасник. Але чи буде це добре працювати?

Пам'ятайте, ми бачили число 7, за яким визначали, на скільки стовпців потрібно розділити?

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

Давайте перевіримо це.

В Додому, натисніть Розширений редактор.

З виразу M видно, що розбиття було здійснено на 7 кодованих колон.

Було б круто, якби ви могли просто змінити число з 7 на, скажімо, 10, розділити комірки з більш ніж 7 значеннями, розділеними комами?

Відповідь полягає в тому, що ви можете. Просто замініть третій параметр функції Таблиця. Розділений стовпець у рядку, який починається з # ”Розділити стовпчик за допомогою розділювача” і використовувати 10, замість списку “Учасники.1 ″,…,” Учасники.7 ”.

Також слід видалити наступний рядок, який виконує Table.TransformColumnTypes ця функція також використовує 7 кодованих імен стовпців.

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

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

Ну, такий підхід не призведе нас до ефективної трансформації, оскільки ми витратимо занадто багато пам'яті та процесора на перетворення багатьох стовпців з нульовим значенням. Крім того, що робити, якщо наше припущення використовувати число 100 буде помилковим?

Розділити та розгорнути - найкращий підхід

Пора побачити найкращий підхід до вирішення нашої проблеми.

Почнемо спочатку та імпортуємо Таблиця1 до редактора запитів.

Тепер ми клацнемо правою кнопкою миші на стовпці Учасники, виберіть Трансформувати і натисніть мала літера (Тут ми не збираємось застосовувати малі трансформації, це лише ярлик для генерування частини потрібної нам формули).

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

Відредагуйте формулу та замініть Текст. Нижче з Splitter.SplitTextByDelimiter (“,”)

Натисніть Введіть після застосування змін, і ви побачите, що стовпець Учасники перетворився на список.

Тепер глибоко вдихніть і приготуйтеся побачити магію в дії - Готовий? Клацніть на кнопку розгортання (Маленька кнопка в заголовку стовпця Учасники).

Це було весело. Нам вдалося дістатися до нашої бажаної таблиці без використання стовпців для розділених значень та без кроку unpivot.

Ми навіть можемо очистити формулу (але це не потрібно, щоб вона працювала), і видалити всі згадки про# "Текст з нижнього регістру" і використовувати СплітУчасники замість цього.

Ось отриманий вираз M:

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

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