Таблиця розмірів у текстовому файлі: рішення Power Query

У цій публікації в блозі я розгляну проблему №2 із поточної проблеми тижня на , де ми обговорювали, як сортувати брудні дані в чисту таблицю розмірів. Це друга ітерація цієї нової ініціативи, яку ми розміщуємо в LuckyTemplates. Я особливо захоплений цією серією, тому що вона дає кожному шанс отримати більше практики на регулярній основі. Ви можете переглянути повне відео цього підручника внизу цього блогу.

У першу середу кожного місяця відбувається перевірка DAX , а в третю середу – перевірка потужних запитів .

Це чудова нагода досліджувати, відкривати та вивчати нові речі про ці мови, які потрібно використовувати в LuckyTemplates.

Ви знайдете категорію «Проблема тижня» на форумі LuckyTemplates.

Основна увага приділяється процесу, а не стільки результату.

Якщо вам вдасться розбити проблему на менші частини, ви зможете вирішити будь-яку проблему, з якою зіткнетеся.

Мелісса де Корте

Раніше Брайан розповідав про техніку під назвою « налагодження гумовою качечкою ». Якщо ви пропустили це, обов’язково подивіться його відео. Це може допомогти вам, коли ви застрягли в цих викликах.

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

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

Але нам також потрібно отримати всі рядки з цього стеку даних і перетворити їх на один рядок для кожної країни. Я називаю це стековими даними, оскільки всі назви полів повторюються в одному стовпці для кожної країни.

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

Зміст

• Зведені таблиці в Excel
• Використання Power Query для створення таблиці розмірів
• Спеціальна функція очищення тексту
• Зведення даних
• Висновок

Зведені таблиці в Excel

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

Потім ви можете ще більше розділити це, перетягнувши поля в розділ стовпців.

Я створив приклад того, як це виглядає. Тут ми маємо майже ті самі дані, що й у текстовому файлі.

А на наступній сторінці я створив порожню зведену таблицю.

Тепер я вам не показав, що у мене тут три колонки замість двох, які я вам уже показав.

Я збираюся перетягнути свій сегмент до рядків.

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

Стовпець 2 містив усі значення полів, тому я збираюся перетягнути їх у розділ значень.

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

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

Тому для мене ключовим перетворенням буде зведення даних, щоб повернути їх у той табличний формат.

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

Використання Power Query для створення таблиці розмірів

Давайте переглянемо запит потужності та переглянемо моє рішення.

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

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

Це моя базова група, створена за допомогою інтерфейсу користувача.

Ви можете побачити, що в застосованих кроках праворуч є багато кроків.

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

Давайте відкриємо розширений редактор.

Ми бачимо, що цей запит має 31 крок.

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

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

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

Другий — основний крок, щоб повернути цей тип даних у правильний табличний формат .

Спеціальна функція очищення тексту

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

Я також додав додатковий спеціальний стовпець. Його єдина мета полягає в створенні моєї спеціальної функції очищення тексту . Я викликав це в об’єднаному стовпці.

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

Давайте подивимося на код M для функції очищення тексту.

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

З цим результатом я виконав усі необхідні перетворення. Деякі з функцій M, які я використовував тут, були надані інтерфейсом користувача, коли я створював свій початковий запит, наприклад Text. Функція обрізки. Однак інші функції не використовувалися.

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

Ви знайдете розділ, присвячений Power Query та функціям.

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

Якщо ви шукаєте конкретну трансформацію, ви можете переглянути це прямо тут.

Зведення даних

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

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

Все, що нам потрібно зробити, це звести самі дані. На вкладці трансформації ви знайдете зведений стовпець. Вибравши стовпець 1, клацніть на Pivot Column.

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

Якщо Excel може працювати з текстовими значеннями, то Power Query також може через налаштування додаткових параметрів. Все, що нам потрібно зробити, це вибрати «Не агрегувати» , щоб він міг обробляти текстові значення.

Як тільки ми натиснемо кнопку OK, ми побачимо, що наші дані були зведені.

Висновок

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

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

Я сподіваюся побачити вас у майбутніх завданнях «Проблема тижня».

Меліса