Як динамічно розширити стовпці в Power Query: покрокова інструкція

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

Відповідно, ваш набір даних матиме таку структуру:

У Power Query після встановлення з'єднання ви отримаєте таблицю, структуровану таким чином:

Тож поки все йде добре.

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

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

Чому це відбувається? Пояснення можна знайти на кроці «Expanded Data» (Розширені дані).

Цей крок автоматично генерує такий код:

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

Щоб розв'язати цю проблему, розгляньмо такий підхід:

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

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

= Table.ExpandTableColumn(#"Removed Other Columns1", "Data", Table.ColumnNames(#"Removed Other Columns1"[Data]{0}))

Тепер наш результат виглядає так:

Для подальшого роз'яснення, чому цей підхід працює, прочитайте пояснення нижче.

Що ми зробили?

Ми замінили наш оригінальний список на динамічно створений список, використовуючи формулу M Table.ColumnNames.

Ось розбір компонентів:

• #"Removed Other Columns1" посилається на наш попередній крок, представляючи набір даних перед розширенням.
• [Data] - це назва стовпця, що містить наші дані.
• {0} вказує номер рядка, де розташовані назви стовпців. Зауважте, що в Power Query нумерація рядків починається з нуля, тому нуль відповідає першому рядку.

Підсумовуючи, якщо ми хочемо забезпечити автоматичне включення нових стовпців у наш набір даних, ми повинні усунути жорстко закодовані назви стовпців і замінити їх динамічно створеними назвами стовпців, використовуючи стандартну формулу Power Query Table.ColumnNames. Ось і все.

Автор: Богдан Дуда