Начиная с 2016 версии Excel, в модели Power Pivot можно создавать Календарь, используя возможности программы.

Для того, чтобы Календарь был создан, необходимо, чтобы в таблицах был хотя бы один столбец с типом данных Дата.

В данной таблице есть столбец Дата поставки с типом данных Дата, но возможность Создать календарь не активна. В чем же причина?

Если внимательно посмотреть, то столбец Дата поставки является Вычисляемым столбцом — это и не позволяет создать календарь. Что же делать в таких случаях, если в исходных данных компоненты даты, на основании которых функцией вычисляется дата? В этом случае, источник надо предварительно обработать через Power Query, а затем загрузить в модель Power Pivot.

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

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

Алгоритм действий:

1. Добавить столбец индекса с 1.

2. Добавить пользовательский столбец, например, Диапазон, с использованием функции List.Range.

List.Range(list as list, offset as number, optional count as nullable number) as list

В результате сформированы списки — каждый раз от 1-го элемента до текущего:

3. Суммировать элементы списка функцией List.Sum. Добавить пользовательский столбец, например, Накопительный итог:

=List.Sum([Диапазон])

4. Удалить лишние столбцы, настроить типы данных — результат готов:

… но это не единственный способ. И в нем есть плюсы и минусы.

Изначально, в настройках Power Query была возможность настройки отключения «типов данных» только в текущем файле: вкладка Данные [Data] — группа Получить и преобразовать данные [Get & Transform Data] — Получить данные [Get Data] — Параметры запроса [Query Options]. В группе Текущая книга [CURRENT WORKBOOK] — Загрузка данных [Data Load] — Определение типов [Type Detection] — Определять типы и заголовки столбцов для неструктурированных источников [Detect column types and headers for unstructured sources]. Большинство пользователей вынуждены или отключать данную опцию в каждом файле или делать отдельный файл-шаблон с нужными настройками.

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

В окне Excel выбрать вкладка Данные [Data] — группа Получить и преобразовать данные [Get & Transform Data] — Получить данные [Get Data] — Параметры запроса [Query Options]. В группе Глобальные [GLOBAL] — Определение типов [Type Detection] — выбрать Никогда не определять типы и заголовки столбцов для неструктурированных источников [Never detect column types and headers for unstructured sources]