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

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

Цей підручник також навчить вас, як заповнювати прогалини у ваших даних і як збирати їх у LuckyTemplates.

Зміст

• Підготовка файлів і макетів у Power Query
• Формулювання обчислень у Power Query
• Заповнення прогалин у датах
• Групування розділених таблиць
• Висновок

Підготовка файлів і макетів у Power Query

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

Тепер перейдіть до Power Query і отримайте файли. Виберіть «Нове джерело» та натисніть «Більше». Потім натисніть «Усі» та виберіть «Папка».

Виберіть Параметр і натисніть OK.

Він покаже вам файли в папці. Вам потрібно зібрати всі файли в одному запиті результату. Натискання «Об’єднати та трансформувати» завантажує дані та створює кілька запитів.

Далі виберіть будь-який файл у папці як файл зразка та виберіть будь-який елемент, який ви хочете почати.

Дивлячись на Query1, ви можете побачити дані як з File1, так і з File2.

Формулювання обчислень у Power Query

Формат даних у Query1 все ще неправильний. Ви можете змінити його за допомогою запиту Transform Sample File.

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

Видаліть перші 2 рядки та стовпець1.

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

Стовпці з датами в рядку формул означають, що якщо в кожному тижні є різна кількість рядків, результатів у цих стовпцях не буде. Отже, видаліть крок Filled Down у розділі Applied Steps.

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

Додайте ручний крок, натиснувши f(x) у рядку формул. Потім введіть Table.ToColumns . Це перетворює кожен стовпець на список зі списками.

Перетворіть список на таблицю та додайте порядковий номер. Ви можете змінити 0 або 1.

Заповнення прогалин у датах

Є ще прогалини в датах, які потрібно заповнити. Однак операція заповнення не працює для списків. Цей список потрібно перетворити на таблицю.

Виберіть стовпець «Індекс» і натисніть «Групувати за». Замініть Count на ToTable, щоб змінити назву стовпця. Змініть RowCount на FromColumns і вкажіть на Column1, де розміщуються списки. Потім перетворіть його на таблицю типів.

Тепер ви бачите, що список перетворився на таблицю лише з одним стовпцем.

Тепер виконайте операцію заповнення для кожної таблиці. Спочатку додайте спеціальний стовпець і назвіть його FillDown .

Вам потрібно отримати доступ до першого значення першого стовпця в таблиці, тому для формули використовуйте IF . Зверніться до стовпця ToTable, який містить таблицю. Введіть Column1 як стовпець, на який ви хочете посилатися. Далі використовуйте null між ними, щоб отримати перше значення зі списку.

Потім перевірте, чи це дата. Якщо так, заповніть цей стовпець таблицею.Filldown. Усередині цієї функції введіть ToTable і Column1. Але якщо це не дата, введіть ToTable, щоб повернути всю таблицю.

Це дасть вам новий стовпець із заповненими таблицями.

Знайдіть, де розміщені дати в стовпцях, і розділіть їх на окремі таблиці. Для цього вам потрібно перетворити таблиці на список стовпців.

Після функцій Table.FillDown і Else у рядку формул посилайтеся на Column1.

Це перетворить ваші таблиці на списки з набором дат.

Далі перетворіть список назад на таблицю, оновивши стовпець індексу. Пам’ятайте, що місце поділу – це місце стовпця дати. Отже, додайте спеціальний стовпець і назвіть його Index2 . Потім перевірте, чи список містить дати.

Використовуйте IF і введіть FillDown. Вам потрібно перевірити лише перше значення у списку. Якщо це дата, поверніть індекс. Якщо ні, введіть null . Потім заповніть цей стовпець.

Групування розділених таблиць

Скористайтеся групою у стовпці Index2. Це групує таблиці з однаковими значеннями Index2 в одну.

Ви не будете рахувати ряди. Отже, змініть RowCount на FromColumns і додайте стовпець FillDown. Потім змініть тип на таблицю.

Якщо клацнути таблицю, ви побачите стовпці в ній.

Далі вам потрібно об’єднати всі окремі таблиці в одну. Створіть ручний крок і використовуйте Table.Combine. Потім посилайтеся на стовпець «Кількість» у ньому.

Виберіть стовпець 1 і стовпець 2. Клацніть «Трансформувати» та виберіть «Скасувати зведення інших стовпців». Потім він покаже вам 4 стовпці.

Видаліть стовпець «Атрибут», оскільки він не потрібен. Потім змініть назви стовпців на Дата , Переміщення та Ім’я . Змініть тип даних стовпця «Дата» на «Дата», а стовпця «Ім’я» — на «Текст».

Тепер ви оновили трансформації у файлі зразка трансформації.

Якщо ви перейдете до Query1, ви побачите, що Column1 таблиці не знайдено. Отже, видаліть крок «Змінений тип» у розділі «Застосовані кроки».

Нарешті, змініть типи даних стовпців. Установіть тип даних стовпця Дата на Дата. Потім встановіть тип даних для стовпців «Рухи» та «Ім’я» на «Текст».

Тепер ви оновили та змінили формат моделі даних на табличну форму.

Висновок

У звіті з даними вам потрібно заповнити прогалини в таблиці дат, перетворивши макети календаря на табличну форму.

Ви можете трансформувати кілька стовпців за допомогою Power Query у LuckyTemplates. Це дасть вашому звіту правильну та цінну інформацію для збору врожаю.

Все найкраще,

Меліса