Тонкощі роботи зі стовпцями в Power Query, Блог, навчальні статті з Microsoft Power BI Desktop

Текст є адаптованим перекладом статей Chris Webb (Кріс Вебб), оригінал — Ensuring Columns Are. Розглядається англомовний Power Query.

Кріс Вебб (Chris Webb) - незалежний експерт, консультант з технологій Analysis Services, MDX, Power Pivot, DAX, Power Query та Power BI. Його блог - це джерело інформації на тему перерахованих технологій. Ось уже понад 10 років він пише про BI-рішення від Microsoft. Кількість його статей перевищила 1000! Також Кріс виступає на великій кількості різних конференцій на кшталт SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays та бере участь у різних спільнотах. Кріс люб'язно дозволив нам перекладати його статті українською мовою. І це одна із них.

Тонкощі роботи зі стовпцями в Power Query

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

Розглянемо наступний файл csv:

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

Припустимо наш запит називається GetSourceData. Припустимо, що вихідні дані мають бути таблицею з трьома стовпцями: Product, Month і Sales. При цьому Product та Month повинні мати текстовий формат, а Sales – числовий. Основні кроки, щоб забезпечити це, навіть у разі зміни структури CSV-файлу, наступні:

1. Створити запит, що підключається до джерела даних, подібно до запиту GetSourceData з наведеного вище прикладу.
2. Створити запит, який завжди повертає таблицю з необхідними стовпцями, але не містить рядків.
3. Додати другу таблицю до першої. Результатом буде таблиця, що містить усі стовпці обох таблиць.
4. Видалити непотрібні стовпці.

Існує кілька способів створення порожньої таблиці на другому етапі. Ви можете використовувати функцію #table(). Наступний рядок коду показує, як це зробити.

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

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

Нехай цей запит називається ExpectedColumns.

Створити третій запит, що додає ExpectedColumns до кінця запиту GetSourceData, є тривіальним завданням. Якщо у запиті GetSourceData є всі очікувані стовпці, то нічого не зміниться. Якщо потрібні стовпці було перейменовано чи видалено, ми отримаємо таблицю, де будуть всі стовпці з обох запитів. Наприклад, перейменуємо Month на Months, отримаємо такий результат:

Зрештою, у третьому запиті виділяємо потрібні стовпці (тобто ті, які є у запиті ExpectedColumns), клацаємо ПКМ та вибираємо пункт Remove Other Columns. Цим ми вилучаємо зайві стовпці. Для попереднього прикладу отримаємо:

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

Код третього запиту:

Як бонус — код ще одного запиту. В ньомупорівнюються стовпці з GetSourceData і ExpectedColumns, після чого виводиться список доданих/віддалених стовпців.

Перевірка наявності стовпців у джерелі даних Power Query

Іноді потрібна проста перевірка наявності всіх необхідних стовпців у джерелі даних. У цьому допоможе функція Table.HasColumns().

Перевіримо наш csv-файл на присутність трьох стовпців Product, Month та Sales. Наступний запит повертає ІСТИНА, якщо в джерелі даних є всі стовпці і брехня в іншому випадку:

---