Power Query: як працювати з динамічними заголовками стовпців

але виникають ускладнення, якщо файли мають різноманітну структуру, як проілюстровано нижче:

Кожна з ваших таблиць містить чотири однакових за назвою стовпці. Функціонал Power Query щодо об’єднання файлів на основі імен стовпців є беззаперечним. Проте реальні назви стовпців відрізняються від стандартних «Стовпець 1», «Стовпець 2» та інших, являючи собою такі значення як «Товар», «Кількість», «Ціна» та «Дата». Ускладнення виникає через невідповідність порядку цих стовпців у різних таблицях. Без додаткових маніпуляцій, об’єднання даних призведе до некоректного порівняння, наприклад, кількості із ціною, що є неприпустимим. Розгляньмо, як за допомогою можливостей Power Query розв’язати цю проблему.

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

Кожна з таблиць містить декілька рядків, розташованих між тимчасовими назвами стовпців («Стовпець1», «Стовпець2» тощо) та їхніми фактичними іменами («Товар», «Кількість» тощо). Нашим першим кроком є пропуск цих рядків, які містять пусті значення. Однак кількість таких рядків варіюється в різних таблицях. Для визначення кількості рядків, які необхідно пропустити, використовується наступна формула мови M:

=Table.AddColumn(#"Removed Columns", "Custom", each List.PositionOf([Content][Column1],"Goods"))

Завдяки цій формулі ми додаємо додаткові стовпці, у яких визначаємо позиції рядків, що містять значення «Товар».

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

Тобто ми кажемо, якщо ти не знаходиш у першому стовпці значення «Goods», то пошукай там значення «Price».

Отже, ми отримали такий результат:

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

= Table.ReplaceValue(#«Added Custom», each [Content],each Table.Skip([Content],[Custom]),Replacer.ReplaceValue,{«Content»})

У результаті отримуємо наступний результат:

Коротке пояснення цієї формули виглядає так: за допомогою формули Table.Skip видали пусті рядки зверху, а саме ту кількість, яку ми порахували попередньою формулою в колонці «Custom».

Також є ще один необхідний крок: підвищення рівня заголовків перед розкриттям таблиці. Для цього використовується наступна формула мови M:

= Table.ReplaceValue(#«Replaced Value»,each [Content],each Table.PromoteHeaders([Content]),Replacer.ReplaceValue,{«Content»})

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

Наступним кроком буде розкриття всіх таблиць:

Й ось ми можемо насолодитися фінальним результатом:

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

1. За допомогою формули List.PositionOf визначати кількість пустих рядків над рядком справжніх заголовків.
2. За допомогою формули Table.Skip видалити ці пусті рядки.
3. За допомогою формули PromoteHeaders зробити верхній рядок заголовками, усунувши тим самим технічні назви «Column1», «Column2» тощо.

І тільки після цих маніпуляцій можна робити розкриття таблиць.