Преобразование макета типа календаря в табличный формат в LuckyTemplates с помощью редактора запросов

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

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

Прежде чем мы начнем, давайте сначала рассмотрим имеющиеся у нас данные.

Здесь у нас есть макет данных, в котором дни расположены горизонтально, а недели — вертикально. Этот тип расположения знаком большинству из нас, поскольку именно так мы можем легко понять данные.

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

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

Оглавление

• Очистка данных для правильного табличного формата
• Разбивая столы
• Идентификация списка для новых таблиц для правильного табличного формата
• Определение того, какие значения принадлежат таблице
• Группировка данных для правильного табличного формата
• Добавление отдельных таблиц
• Заключение

Очистка данных для правильного табличного формата

Чтобы упростить задачу, давайте преобразуем наши данные в редакторе Power Query . Но прежде чем мы это сделаем, давайте удостоверимся, что панель формул видна на вашем экране. Если это не так, перейдите на вкладку «Вид» и установите флажок рядом с панелью формул.

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

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

Давайте также удалим строки с индексами от 14 до 16, так как они полностью заполнены только нулевыми значениями. Снова перейдите на вкладку «Удалить строки» и нажмите «Удалить пустые строки» .

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

Чтобы заполнить эти пробелы, давайте сначала транспонируем нашу таблицу данных, перейдя на  вкладку Transform и нажав кнопку Transpose.

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

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

Это должно дублировать даты и заполнить пробелы в выбранных столбцах.

Разбивая столы

Глядя на данные, которые у нас сейчас есть, мы видим, что есть четыре таблицы, расположенные рядом друг с другом. Столбцы с 1 по 7 составляют одну таблицу, столбцы с 8 по 13 — другую таблицу и так далее.

Чтобы добавить их в одну таблицу, нам нужно выполнить базовую операцию разворота , чтобы преобразовать ее в правильный табличный формат. Но поскольку столы примыкают друг к другу, мы не можем просто так сделать. Мы должны разбить эту таблицу на части и сшить их вместе в таблицы меньшего размера.

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

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

Формула создает список столбцов, которые у нас были ранее. Первое значение списка содержит столбец 1, второе значение содержит столбец 2 и так далее.

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

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

Для этого давайте сначала преобразуем список обратно в таблицу, нажав кнопку «В таблицу» в левом верхнем углу.

Затем добавьте столбец индекса рядом с ним.

Следующее, что мы собираемся сделать, это добавить еще один столбец рядом со столбцом индекса. Этот столбец должен помочь нам определить, какие значения в столбце 1 содержат значения даты. Назовем этот столбец Index2.

Для этого мы включим в Index2 формулу, которая возвращает значение индекса столбца 1, если оно содержит дату.

Мы собираемся использовать , а затем сослаться на Column1. Скобки будут служить оператором; добавление 0 между этими скобками означает, что мы получаем первое значение из списка; затем мы определяем, является ли это датой. Если это так, я хочу, чтобы формула возвращала порядковый номер; если это не так, я не хочу, чтобы он вообще возвращал какое-либо значение.

Поскольку первое значение в Column1 содержит дату, первое значение в Index2 должно содержать порядковый номер 0. Кроме того, поскольку второе значение в Column1 не содержит даты, второе значение в Index2 должно содержать только нулевое значение.

Вот как я установил формулу для Index2. Просто убедитесь, что в вашей формуле нет синтаксических ошибок.

После этого наш столбец Index2 теперь должен иметь индекс значений в столбце 1, который содержит дату. Он также будет включать в себя некоторые нулевые значения в результате формулы, которую мы настроили.

Определение того, какие значения принадлежат таблице

Теперь эти значения индекса внутри Index2 указывают начало и конец отдельных таблиц, которые у нас есть. То есть 0 отмечает начало первой таблицы, 7 отмечает конец первой таблицы, начало второй таблицы и так далее.

Чтобы помочь нам определить, какие значения в Column1 принадлежат одной и той же таблице, давайте применим действие заполнения в нашем новом столбце.

После выполнения действия нулевые значения теперь должны быть заменены нулями, семерками, тринадцатью и двадцатыми. Эти номера указывают на то, что они принадлежат одной таблице. То есть нули образуют одну таблицу, семерки — другую и так далее.

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

Поскольку мы уже знаем, какие значения в Column1 принадлежат одной таблице, давайте теперь сгруппируем их вместе. Для этого мы будем использовать операцию Group By.

Сделав это, мы теперь получим только 4 значения в нашем столбце Index2. Обратите внимание, что рядом с нашим столбцом Index2 находится еще один столбец с именем Count. В этом столбце подсчитывается количество строк в каждом из наших значений в столбце Index2.

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

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

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

При этом все значения в Column1 со значением Index2, равным 0, объединяются в одну таблицу, значения со значением Index2, равным 7, — в другую таблицу и так далее.

Мы проверили каждую запись на значение 0, а затем получили значение списка из Column1. Наконец, мы объединили эти списки в единую таблицу, используя этот М-код.

Мы взяли первые 7 столбцов и сшили их в одну таблицу, взяли следующие несколько столбцов, сшили их в другую таблицу и так далее.

Добавление отдельных таблиц

Теперь, когда мы уже сгруппировали их вместе, давайте попробуем добавить все таблицы, добавив еще один шаг. Опять же, давайте нажмем кнопку fx , чтобы добавить новую формулу.

Операция Table.Combine объединяет список таблиц, а затем создает из них одну таблицу. Здесь мы собираемся сослаться на столбец, содержащий наш список таблиц, который является столбцом Count.

Теперь у нас есть таблица, в которой все вложенные таблицы объединены в одну большую таблицу.

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

После этого удалите столбец атрибутов, так как он нам не нужен. Перейдите к Choose Columns , затем отмените выбор столбца атрибутов.

Затем, наконец, установите правильные типы всех столбцов и правильно пометьте их.

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

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

Затем выберите второй и третий столбцы и установите для них текстовый тип.

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

Заключение

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

Этот учебник предлагает вам много знаний о том, как правильно моделировать данные для облегчения анализа.

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

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

Наслаждайтесь работой над этим!

Мелисса