Блог Excelguru

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

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

окремі

Сценарій, який має користувач тут, - це список значень з одиницями виміру, подібний до цього:

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

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

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

Як ми рекомендуємо в M для Data Monkey, спосіб побудови власної функції - це почати зі звичайного запиту, який дозволить нам пройти через кожен фрагмент, який вам потрібно зробити.

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

  • Створіть новий Power Query -> З інших джерел -> Пустий запит
  • У рядку формул я набрав 1,07 кг (без лапок, лише цей текст) і натиснув Enter
  • Потім я клацнув правою кнопкою миші текст у вікні Power Query та вибрав перетворити його у список

Звичайно, ви не можете зробити тонну зі Списками в інтерфейсі користувача, тому я перетворив їх на таблицю:

  • Інструменти списку -> Перетворення -> У таблицю -> ОК

Чесно кажучи, я міг би розпочати з створення запису або списку з нуля (як ми показуємо вам, як це зробити в M для Data Monkey,), але мені насправді не потрібно було тут, щоб швидко встати і запустити . Незважаючи на це, я зараз сиджу в приємному місці, де весь користувальницький інтерфейс виставлений робити те, що мені потрібно (що було моєю початковою метою).

На даний момент все стає досить легко:

  • Клацніть правою кнопкою миші Стовпець1 -> Замінити значення -> Замінити 0 нічим
  • Повторіть з 1 по 9 та десятковий символ

Це видалило всі цифри та десяткові знаки, залишивши лише текст. Але оскільки я знаю, що в деяких цінностях також є пробіли, мені слід з цим розібратися:

  • Клацніть правою кнопкою миші Стовпець1 -> Перетворення -> Обрізати

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

  • Клацніть на fx ліворуч у рядку формул
  • Додайте до тексту в рядку формул наступне: [Стовпець1]

Зверніть увагу, що тепер у нас є лише точка даних, а не заголовок Column1.

Тепер у нас є акуратна маленька функція, яка дозволить мені взяти точку даних, дезінфікувати її і перетворити на точку даних без провідних значень. Але як я можу переназначити, щоб використовувати його для кожного запису? Відповідь полягає в тому, щоб перетворити цей запит на спеціальну функцію, як ми описуємо в главі 22 M для Data Monkey. Ось як ми це робимо:

  • Перейдіть до Перегляд -> Розширений редактор
  • Прямо перед рядком “дозвольте” додайте таке:

  • Перейдіть і поставте два/символи перед поточним рядком Source, щоб прокоментувати його (інакше це перезапише вхід функції)
  • Натисніть Готово
  • Перейменуйте запит на fxRemoveNumbers

Це воно. Ми перетворили його на функцію. Тепер ви можете перейти до Домашня сторінка -> Закрити та завантажити, щоб зберегти її, і вона готова до використання. Найцікавішим тут є те, що створення логіки - це важка частина, перетворити її на функцію - смертельно просто.

Тож тепер давайте використаємо нашу нову функцію для розділення значень та тексту. Ось як я це зробив:

  • Виділіть будь-яку комірку в таблиці -> створіть новий запит -> З таблиці
  • Перейдіть до пункту Додати стовпець -> Додати спеціальний стовпець
    • Назва нового стовпця: Вимірювання
    • Формула стовпця: fxRemoveNumbers ([Кількість])

І ми отримали приємний новий стовпець із лише текстовими значеннями.

Непогано, тепер нам просто потрібно знайти спосіб замінити відповідний текст у стовпці Кількість нічим ... Перевіривши керівництво формули Power Query від MSDN, я знайшов формулу під назвою Text.Replace (), яка, здається, повинна робити саме це:

  • Перейдіть до пункту Додати стовпець -> Додати спеціальний стовпець
    • Назва нового стовпця: Значення
    • Формула стовпця: = Text.Replace ([Кількість], [Міра], ")

Підсумовуючи тут, ми розглянемо, що є в стовпці Кількість, і замінимо будь-який екземпляр тексту в стовпці Вимірювання значенням між двома наборами лапок (тобто нічим.) Результати показані нижче:

Тепер просто провести чистку:

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

І ось ти йдеш. Готово. Нам просто потрібно перейти до Домашнього -> Закрити та Завантажити, щоб зробити це, а потім оновити його в будь-який час, коли нам це потрібно.

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