Скажімо, у нас є файл Excel із кількома розумними таблицями:
Якщо завантажити ці таблиці в Power Query стандартним способом за допомогою команди Дані – Отримати дані – З файлу – З книги (Дані — Отримати дані — З файлу — З книги), тоді ми отримуємо щось на зразок цього:
Зображення, я думаю, знайоме багатьом користувачам Power Query. Подібні вкладені таблиці можна побачити після комбінування запитів (а-ля VLOOKUP), групування (команда Групувати за таб Перетворення), імпортування всіх файлів із заданої папки тощо.
Наступним логічним кроком у цій ситуації зазвичай є розгортання всіх вкладених таблиць одночасно – за допомогою кнопки з подвійними стрілками в заголовку стовпця дані:
В результаті ми отримуємо збірку всіх рядків з усіх таблиць в єдине ціле. Все добре, просто і зрозуміло.
Тепер уявіть, що новий стовпець (Discount) був доданий у вихідні таблиці та/або один із існуючих (City) був видалений:
Тоді наш запит після оновлення поверне не дуже красиву картинку – знижка не з'явилася, а графа міста стала порожньою, але не зникла:
І легко зрозуміти чому – у рядку формул ви чітко бачите, що назви розгорнутих стовпців жорстко закодовано в аргументах функції Table.ExpandTableColumn у вигляді списків у фігурних дужках.
Обійти цю проблему легко. Спочатку давайте за допомогою функції отримаємо назви стовпців із заголовка будь-якої (наприклад, першої) таблиці Таблиця.Назви стовпців. Це буде виглядати так:
тут:
- #”Інші стовпці видалено” – назва попереднього кроку, звідки беремо дані
- 0 {} – номер таблиці, з якої витягуємо заголовок (рахуємо від нуля, тобто 0 – перша таблиця)
- [Дані] – назва колонки на попередньому кроці, де розташовані розгорнуті таблиці
Залишається підставити отриману конструкцію в рядку формул у функцію Table.ExpandTableColumn на етапі розширення таблиць замість жорстко закодованих списків. У підсумку все має виглядати так:
Це все. І більше не буде проблем з розширенням вкладених таблиць при зміні вихідних даних.
- Створення багатоформатних таблиць з одного аркуша в Power Query
- Створюйте таблиці з різними заголовками з кількох файлів Excel
- Збір даних з усіх аркушів книги в одну таблицю