Посібник Microsoft Power Query про те, як виправити змішані проблеми фіксованої ширини стовпця

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

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

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

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

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

Меліса: Перша порада: якщо ви переглядаєте файл фіксованої довжини, ви можете перейти на вкладку «Перегляд» і ввімкнути параметр «Моноширинний» .

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

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

Я створив параметр для розташування файлу, де я зберіг файл CSV. Я вніс це як проміжний файл і додав його до свого параметра розташування файлу. Тоді я створив довідник і працюватиму на основі цього довідника. Ось що ми зараз розглядаємо в Microsoft power query.

Зміст

• Додавання стовпця індексу
• Створення списку буферів у Microsoft Power Query
• Виділення заголовків із рядків
• Видалення нулів і пробілів у Microsoft Power Query
• Розбиття розділів
• Обрізання текстових рядків у Microsoft Power Query
• Висновок

Додавання стовпця індексу

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

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

Нам знадобиться ключ, щоб зібрати ці речі назад. Але якщо ключа немає, я хочу додати . Я клацну піктограму міні-таблиці, виберу «Додати стовпець індексу» , а потім додам «З 0» .

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

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

Для початку скопіюємо це значення:

Додайте новий порожній запит, вставте його та назвіть це HeaderID .

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

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

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

Давайте ввімкнемо завантаження для цих двох запитів.

Створення списку буферів у Microsoft Power Query

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

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

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

Я назву цей BufferList і посилаюся на Column1. Щоб завантажити його в пам'ять, я додам крок List.Buffer .

Ось моя змінна вгорі. Я можу посилатися на це знову і знову.

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

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

У результаті він знайшов текст і повернув 5 і 23.

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

У цьому випадку я додав Table.FillDown і в текстовому рядку вказав, який стовпець ми хочемо заповнити (Header).

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

Виділення заголовків із рядків

Наступним кроком буде відокремлення заголовків від рядків. Я додам ще один спеціальний стовпець і назву його Temp . Цього разу ми збираємося зробити щось більш складне та використовувати той BufferList , який я створив раніше.

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

Я почну з оператора if і використовую List.Contains для пошуку конкретної позиції в BufferList і посилання на запит HeaderID .

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

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

Нам також потрібно визначити секцію рядка. Якщо список містить не HeaderID , а DetailID , тоді ми знаходимося в розділі рядка.

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

Ця опозиція отримала рядок заголовка та повернула H, а потім знайшла детальний рядок і повернула R. Потім повернула 0 для всіх елементів, які є спільними в цьому розділі рядка.

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

Ми зробимо це в рядку формул і знову використаємо Table.FillDown . Йому потрібен список із назвою стовпця, який є нашим стовпцем Temp .

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

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

Видалення нулів і пробілів у Microsoft Power Query

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

Розбиття розділів

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

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

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

Я можу вибрати Стовпець1 , перейти до панелі формул, вибрати Розділити стовпець , а потім розділити за позиціями.

Потім нехай Power Query сам розбереться з цим. Він запропонує пару позицій. Натисніть OK , щоб прийняти ці позиції.

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

Тут у рядку формул я можу перейменувати їх за допомогою Item і Job # . Це позбавить мене від наступного кроку перейменування стовпця.

Після цього кроку все, що мені потрібно зробити, це вибрати Item , вибрати Job # і, звичайно, вибрати наш ключ заголовка . Тоді я видалю всі інші стовпці, тому що вони мені більше не потрібні.

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

Тож ми відкриваємо це та знімаємо виділення з тире та елементів.

Тепер усі заголовки готові.

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

Ми повернемося до початкового запиту, який почали. Ми почали з «Відфільтрованих рядків» на панелі «Застосовані кроки».

Я скопіюю це та додам до свого фільтра. Цього разу я вибираю не H, а R.

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

Ось що пропонує Power Query. Давайте спробуємо.

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

Обрізання текстових рядків у Microsoft Power Query

Я виберу цей перший стовпець, а потім натисну клавішу вниз + Shift, щоб вибрати стовпець 1.10. Перейдіть до Transform , виберіть Format , потім Trim . Обрізання видаляє лише зайві проміжки спереду або на кінці струни, а не між ними.

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

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

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

Для цього ми можемо використати самозлиття, щоб ми могли об’єднати таблицю саму з собою, щоб знову об’єднати цю інформацію. На вкладці «Головна» виберіть «Об’єднати» , потім виберіть «Стовпець 5» і той самий запит.

Замість AllDetails я хочу AllHeaders як початкову таблицю, яку я хочу об’єднати.

Це повернуло всю інформацію з таблиці заголовків з одним рядком для кожного елемента та кожного номера завдання.

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

Ми видалимо тут останній стовпець, а потім завершимо виправлення змішаної фіксованої ширини стовпця в Microsoft power query.

Висновок

У цьому посібнику ми придумали спосіб вирішення змішаних проблем із фіксованою шириною стовпців за допомогою Microsoft Power Query. Якщо вам сподобався вміст, розглянутий у цьому посібнику, не забудьте підписатися на телеканал LuckyTemplates.

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

Меліса