Як динамічно об’єднати стовпці в таблиці Power Query

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

У цьому сценарії йдеться про експорт необроблених даних, у результаті чого дані потрапляють у невідому кількість сусідніх стовпців. Коли ви дивитеся на приклад, ви можете бачити, що опис перекинувся в стовпець 4, а опис 2 було розділено на два сусідні стовпці. Але так буде не завжди. У наступному циклі ця кількість стовпців може змінитися.

Перш ніж перейти до вирішення, давайте розглянемо код M, який Power Query генерує, коли ви об’єднуєте стовпці.

Зміст

• Power Query Table M Code
• Об’єднання змінної кількості стовпців у таблиці Power Query
• Висновок

Power Query Table M Code

Існує кілька способів об’єднати стовпці. Перший – вибрати стовпець опису та натиснути клавішу Shift або Control, а потім вибрати також стовпець 4. Потім клацніть правою кнопкою миші та виберіть «Об’єднати стовпці» .

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

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

У рядку формул ми бачимо код M, створений цим кроком перетворення.

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

Тож функція, яку Power Query використовує для об’єднання стовпців, — Table.CombinedColumns . Перший параметр, який він приймає, це таблиця. Цю таблицю повертає попередній крок у нашому коді, тому в Застосованих кроках ви побачите, що це називається Source .

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

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

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

Об’єднання змінної кількості стовпців у таблиці Power Query

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

А щоб отримати назви стовпців у рядку формул, я можу додати Table.ColumnNames . Ця функція повертає список із усіма назвами стовпців із цієї таблиці. Давайте знову перетворимо це на таблицю, натиснувши «До таблиці» .

У цьому спливаючому вікні натисніть OK.

Потім на вкладці «Додати стовпець » я виберу «Формат» , а потім «Обрізати» .

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

Тож у рядку формул, замість Text.Trim , ми можемо сказати if Text.StartsWith , а потім вказати його на наш стовпець 1 , і я негайно скопіюю це. Йому потрібен текст, який ми шукаємо, тож це буде Стовпець. Отже, якщо він починається з текстового стовпця, ми хочемо «null» — для всього іншого ми хочемо все, що є в стовпці 1. Ми також можемо перейменувати цей стовпець, тож у рядку формул, замість Trim, назвемо його GroupColumn .

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

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

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

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

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

Отже, якщо ми виконаємо цей фільтр для опису всередині стовпця 1, ми отримаємо стовпці, які відповідають цим критеріям.

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

І тепер ми отримуємо список. Отже, давайте дамо цьому запиту правильну назву ( ListDescr ) і перевіримо, чи вимкнено його завантаження.

Тепер ми можемо повернутися до запиту Result і замінити другий аргумент нашим динамічним списком. Отже, тут ми можемо посилатися на наш ListDescr .

Давайте повторимо це також для опису 2. Я перейду до свого запиту ColumnGroups. Я створю ще одне посилання та виберу потрібні мені стовпці. Тоді я відфільтрую опис 2, який поверне ці три стовпці. Я клацну правою кнопкою миші свій заголовок і виберу «Деталізація», а також перейменую цей запит ( ListDescr2 ).

Потім я повернуся до свого запиту результатів, виберу стовпець Опис2 і натисну клавішу shift або control, щоб також вибрати наступний стовпець (стовпець2). Я клацну правою кнопкою миші заголовок і виберу.

У спливаючому вікні я виберу вкладку як роздільник і назву це Опис2.

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

Висновок

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

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

Все найкраще!

Меліса