Блог Excelguru

Більше вигадливих речей від автора www.excelguru.ca…

У дописі цього тижня ми повернемося до початкового допису про те, як відокремлювати значення та текст у Power Query від 4 листопада 2015 р. Цей допис привернув кілька пропозицій Імке Фельдмана та Білла Шиша щодо кращих методів.

Інші публікації на тему

Усі три останні публікації тісно пов’язані між собою, і, як нагадування, це, звичайно, залежить від вашої конкретної ситуації. Ось три ітерації:

Моя оригінальна публікація, щоб розділити вимірювання, залишивши лише цифри (це буде працювати, лише якщо в вимірі немає цифр.)
Зберігання цифр (або тексту) з використанням підходу Імке Фельдмана
Ця публікація з використанням методу Білла Шиша для розділення вимірювань

То чому нам потрібно це ще раз вивчати? Ну, реальність така, що розроблене мною рішення ідеально працювало для набору даних, який я використовував. Однак Білл знущався з різних даних, які виглядали так:

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

Якщо ви спробували мій метод, то виявите, що він не вдається, як показано нижче:

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

Я трохи відійшов від початкової подачі Білла і побудував його таким чином, що, на мою думку, більшість користувачів підійде до цього, як ви побачите. (Початкове подання Білла було трохи досконалішим, але я показую, як би я прибув туди, намагаючись побудувати те, з чим він закінчився.)

Крок 1: Втягніть дані

Звичайно, для початку нам потрібні дані ...

Створіть новий запит -> З таблиці
Клацніть правою кнопкою миші стовпець Кількість -> Трансформувати -> нижній регістр

Цей останній крок насправді досить важливий. Причина полягає в тому, що ми зараз хочемо розділити дані на першому етапі символу між ними a і z. Оскільки Power Query чутливий до регістру, примушування тексту до нижнього регістру означає, що ми не пропустимо розбиття на основі символу в A до Z встановити. Це також означає, що ми надаємо Power Query менше обробки, оскільки він повинен шукати лише 26 символів, а не 52 (як малі, так і великі регістри).

Крок 2: Окремі значення та текст

Тепер, коли ми знаємо, що хочемо робити, давайте це зробимо. Давайте розділимо текст на перший альфа-символ:

Перейдіть до пункту Додати стовпець -> Додати спеціальну колонку
- Назва нового стовпця -> Значення
- Формула спеціального стовпця:

Ця формула досить цікава, оскільки вона буде розділена на один із символів між цитатами. Оскільки ми змусили текст писати з малої літери, він буде реагувати на будь-яку букву алфавіту з a-z або A-Z. Але є одна невеличка проблема ... вона повертає список, а не текст:

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

Клацніть на значок шестірні поруч із кроком Доданий власний у вікні Застосовані кроки
Змініть формулу таким чином:

Пам’ятаючи, що Power Query відлічується від нульової бази, і що число між фігурними дужками дозволяє нам детально переглянути детальний список у списку, ми повертаємо список, що включає лише перший елемент, наступним чином:

Після цього ми можемо витягнути решту значень справа, використовуючи деякі текстові функції. (Ви можете дізнатись більше про це в моєму дописі на темі 5 Дуже корисних формул тексту - видання Power Query, або прочитавши Розділ 17 «M» для Data Monkey)

Перейдіть до пункту Додати стовпець -> Додати спеціальну колонку
- Назва нової колонки -> Вимірювання
- Формула спеціального стовпця:

На даний момент ми можемо визначити проблему в тому, як ми пройшли процес. Ти це бачиш?

У вихідному наборі даних величина L (для літрів) була написана великими літерами. У нашому випуску це не так. Якщо вам все одно до цього, пропустіть крок 3, але якщо ви вважаєте це важливим ... нам потрібно трохи змінити наші кроки.

Крок 3: Виправте нижчі регістри

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

Так як? Якщо нам потрібно його перетворити, щоб розділити на менший список, що нам потрібно робити?

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

Клацніть на піктограму шестірні поруч із Доданим спеціальним кроком
Змініть формулу таким чином:

= Text.SplitAny (Text.Lower ([Кількість]), "abcdefghijklmnopqrstuvwxyz")

Тепер видалімо крок нижнього регістру тексту і подивимося, чи він все ще працює (не забудьте вибрати крок Доданий спеціальний1 після видалення кроку нижнього регістру:

Примітка: Якщо ви очікуєте цього з самого початку, очевидно, що немає необхідності перетворювати на малі літери, спричиніть помилку, а потім виправте її. Ви можете пропустити біль і просто обернути стовпець у функцію Text.Lower () для початку. Причиною того, що я показав цей маршрут, є те, що я часто ітератую таким чином, будуючи власні сценарії очищення даних.

Крок 4: Остаточне очищення

Залишилось лише перетворити стовпець Value у числа. Якщо ви спробуєте, ви отримаєте повідомлення про помилку, оскільки посередині деяких цифр все ще залишаються пробіли (коми - це нормально, а пропуски - ні).

Клацніть правою кнопкою миші стовпець Значення -> Замінити значення
- Значення для пошуку -> один пробіл
- Замінити на -> залиште цю область порожньою
Клацніть правою кнопкою миші стовпець Значення -> Тип зміни -> Десяткове число

Перейменуйте свій запит
Перейдіть на головну сторінку -> Закрити та завантажити