rss
  •  

Нарастающий итог в Power Query

| Категория: PowerQuery, Надстройки Power, Приемы и советы |

0

Нарастающий итог в 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

| Категория: PowerQuery, Надстройки Power, Приемы и советы |

0

Изначально, в настройках 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]

Как по запросу PowerQuery построить сводную таблицу

| Категория: PowerQuery, Надстройки Power |

0

В 2019 версии в диалоговом окне выгрузки результата запроса PowerQuery расширили выбор — помимо Таблица или Только создать подключение добавили Отчет сводной таблицы и Сводная диаграмма:

А что делать в более ранних версиях, где в подобном окне нет и намека на сводную таблицу или сводную диаграмму??

В этом случае, стоит выбрать в окне выгрузки запроса Только создать подключение [Only Create Connection], а затем начать строить сводную таблицу обычным образом: вкладка Вставка [Insert] — Сводная таблица [PivotTable] — Использовать внешний источник данных [Use an external data source], нажать кнопку Выбрать подключение [Choose Connection]:

В окне существующих подключений выбрать нужное:

А далее — всё как обычно!