Макеты календаря в табличный формат с помощью 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 строки и Column1.

Затем заполните пробелы внутри таблицы. Перейдите на вкладку Transform и выберите Transpose. Выберите столбцы, содержащие даты, и щелкните заголовок. Нажмите «Заполнить» и выберите «Вниз».

Столбцы с датами в строке формул означают, что если в каждой неделе имеется различное количество строк, результаты не будут в этих столбцах. Итак, удалите шаг «Заполнено вниз» в разделе «Примененные шаги».

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

Добавьте шаг вручную, нажав f(x) в строке формул. Затем введите Table.ToColumns . Это превращает каждый столбец в список, содержащий списки.

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

Заполнение пробелов в датах

Есть еще пробелы в датах, которые вам нужно заполнить. Однако операция заполнения не работает для списков. Вам нужно превратить этот список в таблицу.

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

Теперь вы можете видеть, что список превратился в таблицу только с одним столбцом.

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

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

Затем проверьте, является ли это датой. Если это так, заполните этот столбец с помощью Table.Filldown. Внутри этой функции введите ToTable и Column1. Но если это не дата, введите ToTable, чтобы вернуть всю таблицу.

Это даст вам новый столбец с заполненными таблицами.

Найдите, где размещены даты столбцов, и разбейте их на отдельные таблицы. Для этого вам нужно превратить таблицы в список столбцов.

После функций Table.FillDown и Else в строке формул укажите ссылку Column1.

Это превратит ваши таблицы в списки с набором дат.

Затем снова превратите список в таблицу, обновив столбец индекса. Помните, что место разделения находится там, где находится позиция столбца даты. Итак, добавьте пользовательский столбец и назовите его Index2 . Затем проверьте, содержит ли список даты.

Используйте ЕСЛИ и введите FillDown. Вам нужно только проверить первое значение в списке. Если это дата, вернуть Index. Если нет, введите ноль . Затем заполните этот столбец.

Группировка отдельных таблиц

Используйте Group by для столбца Index2. Это группирует таблицы с одинаковыми значениями Index2 в одну.

Ты не будешь считать ряды. Итак, измените RowCount на FromColumns и добавьте столбец FillDown. Затем измените тип на таблицу.

Если щелкнуть таблицу, вы увидите столбцы внутри нее.

Затем вам нужно объединить все отдельные таблицы в одну таблицу. Создайте шаг вручную и используйте Table.Combine. Затем сошлитесь на столбец Count внутри него.

Выберите Столбец1 и Столбец2. Нажмите «Преобразовать» и выберите «Развернуть другие столбцы». Затем он покажет вам 4 столбца.

Удалите столбец атрибутов, так как он не нужен. Затем измените имена столбцов на Date , Movements и Name . Измените тип данных столбца «Дата» на «Дата» и столбца «Имя» на «Текст».

Теперь вы обновили преобразования в образце файла преобразования.

Если вы перейдете к Query1, вы увидите, что Column1 таблицы не может быть найден. Поэтому удалите шаг «Измененный тип» в разделе «Примененные шаги».

Наконец, измените типы данных столбцов. Установите тип данных столбца «Дата» на «Дата». Затем установите для столбцов «Движения» и «Имя» тип данных «Текст».

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

Пользовательские календари LuckyTemplates: расчет изменения месяца за месяц — 445 календарей
Пользовательские методы условного форматирования в LuckyTemplates
Как работать с несколькими датами в LuckyTemplates

Заключение

В своем отчете с данными вы должны заполнить пробелы в таблице дат, превратив макеты календаря в табличную форму.

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

Всего наилучшего,

Мелисса