Розділити/сегментувати/розділити/розділити вашу таблицю в Power BI/Power Query

Нижче наведено досить поширений сценарій, який я бачив із площими файлами, експортованими із застарілих систем, але він також застосовується до багатьох інших сценаріїв. Він використовує мову M всередині Power BI Desktop/Power Query, але ви можете піти далі, не знаючи про це багато.

Сценарій: таблиця, що містить кілька сегментів або розділів

Уявіть наступну таблицю:

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

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

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

Рішення: Спліт функціонує як порятунок

Ви можете завантажити зразок файлу, натиснувши кнопку нижче:

Не забувайте, що ми почнемо з таблиці, яка виглядає так:

Крок 1: Розбиття таблиці на кілька менших таблиць однакового розміру

Оскільки наш перший крок у нашому запиті називається “Джерело”, все, що нам потрібно зробити, це застосувати формулу Таблиця. Спліт (джерело) як новий спеціальний крок (натиснувши кнопку fx у рядку формул), і це буде виглядати так:

Це по суті перетворює нашу таблицю на Список таблиць, де кожне значення в цьому списку є в основному таблицею. Номер 3, який ви бачите у цій формулі, це тому, що наш шаблон зазначав, що кожні 3 рядки був новий "запис" або "об'єкт".

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

Крок 2: Використання спеціальної функції для таблиць у списку

У мене вже є функція у файлі, яка називається myFx що перетворить кожну таблицю з цього списку в таблицю, яку ми шукаємо. Для цього ми створюємо новий спеціальний крок і вводимо формулу List.Transform (Custom1, myFx) який застосовуватиме нашу спеціальну функцію до кожного елементу у списку.

Результат цього виглядає так:

Крок 3: Об’єднайте всі таблиці

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

Найпростіший спосіб - використовувати Таблиця.Комбінат функція, як показано на наступному зображенні:

І саме так ми маємо вихідну таблицю, яку ми шукаємо.

Про інші закономірності для цього сценарію

Можливо, ви помітили, що цей сценарій також можна вирішити за допомогою деяких моделей, які Кен Пулс і я опублікували в нашій книзі "M is for Data Monkey", де ми використовуємо такі речі, як стовпець індексу, модуль, ціле розділення та інші, які поза межами кнопки в інтерфейсі Power Query.

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

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

Ми, швидше за все, додамо ці нові шаблони до нашої Академії Power Query як відео та до Рецептів Power Query, тож будьте уважні до цих.

Спеціаліст Excel перетворився на спеціаліста з BI, використовуючи найновіші інструменти від Microsoft для BI - Power BI. Він є співавторомМ - для Data Monkey', Блогер, а також Youtuber потужних відео-фокусів Excel.

Його визнали

Сертифікований спеціаліст Microsoft (MCP - MCSA: звітування про BI)
Сертифікований тренер Microsoft (MCT)
і є одним з міжнародних піонерів у Power Pivot
Power Query та Power BI.