Перетворення макета типу календаря на табличний формат у LuckyTemplates за допомогою редактора запитів

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

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

Перш ніж ми почнемо, давайте спочатку перевіримо дані, які у нас є.

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

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

З огляду на це, давайте перетворимо дані у відповідний формат таблиці, де ми отримаємо стовпець для дат, стовпець для рухів і ще один стовпець для імен.

Зміст

• Очищення даних для правильного табличного формату
• Розбивання столів
• Визначення списку для нових таблиць для правильного табличного формату
• Визначення значень у таблиці
• Групування даних для правильного табличного формату
• Додавання окремих таблиць
• Висновок

Очищення даних для правильного табличного формату

Щоб полегшити роботу, давайте перетворимо наші дані всередину. Але перш ніж ми це зробимо, давайте переконаємося, що рядок формул видно на вашому екрані. Якщо ні, перейдіть на вкладку «Перегляд» і встановіть прапорець біля рядка формул.

Перший крок — видалити непотрібні значення з наших даних. Давайте видалимо перший стовпець і два верхніх рядки наших даних, оскільки вони нам насправді не потрібні. Для цього перейдіть на вкладку «Видалити рядки», клацніть «Видалити верхні рядки», введіть 2 і натисніть «ОК».

А тепер, щоб видалити перший стовпець, перейдіть на вкладку «Вибрати стовпці», а потім зніміть вибір зі стовпця 1, знявши прапорець біля нього.

Давайте також видалимо рядки з індексами 14–16, оскільки вони повністю заповнені лише нульовими значеннями. Знову перейдіть на вкладку «Видалити рядки» , а потім натисніть «Видалити порожні рядки» .

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

Щоб заповнити ці прогалини, давайте спочатку транспонуємо нашу таблицю даних, перейшовши на  вкладку «Трансформувати» , а потім натиснувши кнопку «Транспонувати».

Тепер стіл має виглядати так, ніби його перевернули на бік після виконання дії.

Далі виберіть усі стовпці, які містять дати, клацніть правою кнопкою миші заголовок стовпця та виберіть «Заповнити».

Це має повторити дати та заповнити прогалини у вибраних стовпцях.

Розбивання столів

Дивлячись на дані, які ми зараз маємо, ми бачимо, що є чотири таблиці, які прилягають одна до одної. Стовпці з 1 по 7 утворюють одну таблицю, стовпці з 8 по 13 утворюють іншу таблицю і так далі.

Щоб додати їх в одну таблицю, нам потрібно виконати базову операцію розведення , щоб перетворити її в правильний табличний формат. Але оскільки таблиці розташовані поруч одна з одною, ми не можемо це зробити просто так. Ми повинні розбити цю таблицю на частини та зшити їх разом у менші таблиці.

Для цього ми створимо першу формулу для цього підручника. Давайте клацнемо кнопку fx і додамо формулу, яку будемо використовувати. Ми будемо використовувати операцію Table.ToColumns . Це створює список вкладених списків значень стовпців із таблиці. Кожен стовпець буде перетворено на список.

Ось як я встановив формулу.

Формула створює список стовпців, які у нас були раніше. Перше значення списку містить стовпець 1, друге значення містить стовпець 2 і так далі.

Визначення списку для нових таблиць для правильного табличного формату

Тепер ми повинні визначити, які значення в списку містять значення дати. Коли ми знаємо, де розташовані ці дати, ми можемо визначити, де починається або закінчується кожна з наших окремих таблиць.

Щоб зробити це, давайте спочатку перетворимо список назад на таблицю, натиснувши кнопку «До таблиці» у верхньому лівому куті.

Потім додайте поруч стовпець індексу.

Наступне, що ми збираємося зробити, це додати ще один стовпець поруч зі стовпцем індексу. Цей стовпець повинен допомогти нам визначити, які значення в стовпці 1 містять значення дати. Назвемо цей стовпець Index2.

Для цього ми збираємося включити формулу в Index2, яка повертає значення індексу Column1, якщо він містить дату.

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

Оскільки перше значення в стовпці 1 містить дату, перше значення в індексі 2 має містити номер індексу 0. Крім того, оскільки друге значення в стовпці 1 не містить дати, друге значення в індексі 2 має містити лише нульове значення.

Ось як я встановив формулу для Index2. Просто переконайтеся, що у вашій формулі немає синтаксичних помилок.

Після цього наш стовпець Index2 тепер має мати індекс значень у Column1, який містить дату. Він також включатиме деякі нульові значення в результаті формули, яку ми встановили.

Визначення значень у таблиці

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

Щоб допомогти нам визначити, які значення в стовпці 1 належать одній таблиці, застосуємо дію заповнення в нашому новому стовпці.

Після виконання дії нульові значення тепер мають бути замінені нулями, сімками, тринадцятьми та двадцятками. Ці цифри вказують на те, що вони належать до однієї таблиці. Тобто нулі утворюють одну таблицю, сімки — іншу таблицю і так далі.

Групування даних для правильного табличного формату

Оскільки ми вже знаємо, які значення в стовпці 1 належать до однієї таблиці, давайте згрупуємо їх разом. Для цього ми будемо використовувати операцію Group By.

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

Однак насправді це не те, що ми хочемо робити. Нам потрібно створити нову таблицю, використовуючи наявні стовпці.

Для цього ми скористаємося операцією Table.FromColumns , посилаємось на стовпець, який містить значення нашого списку, тобто Column1, а потім змінюємо тип повернення на таблицю.

Важливо переконатися, що ми вказали правильний стовпець у нашій формулі. Перевірте, чи дійсно стовпець 1 містить значення списків.

Це зшиває всі значення в стовпці 1 зі значенням Index2 0 в одну таблицю, значення зі значенням Index2 7 в іншу таблицю і так далі.

Ми перевірили кожен запис на значення 0, а потім отримали значення списку зі стовпця1. Нарешті, ми зшили ці списки, щоб сформувати єдину таблицю, використовуючи цей код M.

Ми взяли перші 7 стовпців і з’єднали їх в одну таблицю, захопили кілька наступних стовпців, з’єднали їх з іншою таблицею і так далі.

Додавання окремих таблиць

Тепер, коли ми вже згрупували їх разом, давайте спробуємо додати всі таблиці, додавши ще один крок. Давайте знову натиснемо кнопку fx , щоб додати нову формулу.

Операція Table.Combine об’єднує список таблиць, а потім створює з них одну таблицю. Тут ми посилатимемося на стовпець, який містить наш список таблиць, тобто стовпець «Кількість».

Тепер у нас є таблиця, де всі вкладені таблиці додаються назад в одну велику таблицю.

Після додавання всіх таблиць ми можемо виконати кілька останніх кроків для перетворення наших даних у правильний табличний формат. Виберіть перші два стовпці вашої таблиці, а потім натисніть кнопку «Відмінити розведення інших стовпців» на вкладці «Трансформація».

Після цього видаліть стовпець атрибутів, оскільки він нам не потрібен. Перейдіть до «Вибрати стовпці» , а потім зніміть вибір зі стовпця атрибутів.

Потім, нарешті, встановіть правильні типи всіх стовпців і позначте їх належним чином.

Двічі клацніть заголовок кожного стовпця, щоб змінити його назву відповідно.

Для першого стовпця змініть його тип на дату, оскільки він містить наші значення дати.

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

Все зроблено! Ось як має виглядати наш правильний табличний формат.

Висновок

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

Цей підручник пропонує вам багато знань про те, як правильно моделювати дані для легшого аналізу.

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

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

Приємної роботи над цим!

Меліса