Power Query Практический №4. Таблица календаря (Таблица дат)

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

Для построения отчетов в модели данных Excel вам нужно создать таблицу дат. Сейчас вы узнаете как это сделать в Power Query.

В этом уроке мы научимся создавать таблицу-календарь в Excel с помощью Power Query. Календарь будет динамическим, т. е. будет расширяться со временем автоматически.

Решение

Для решения вам пригодится следующее:

• Excel.CurrentWorkbook()
• Table.FromList
• Number.From
• Splitter.SplitByNothing()
• Date.WeekOfYear
• Day.Monday

Сначала мы сгенерируем список дат и преобразуем его в таблицу. Далее мы просто создадим столбцы с компонентами даты (date parts).

Примененные функции

• Table.FromList
• Number.From
• Splitter.SplitByNothing
• Table.TransformColumnTypes
• Table.AddColumn
• Date.Year
• Int64.Type
• Date.Month
• Date.Day
• Date.QuarterOfYear
• Date.WeekOfYear
• Date.DayOfWeek

Код

let
   start = Excel.CurrentWorkbook(){[Name = "Параметры"]}[Content]{0}[
      Значение
   ], 
   finish = Excel.CurrentWorkbook(){[Name = "Параметры"]}[Content]{1}[
      Значение
   ], 
   tab_generate = Table.FromList(
      {Number.From(start) .. Number.From(finish)}, 
      Splitter.SplitByNothing(), 
      {"Дата"}
   ), 
   col_type = Table.TransformColumnTypes(
      tab_generate, 
      {{"Дата", type date}}
   ), 
   tab_add_col_1 = Table.AddColumn(
      col_type, 
      "Год", 
      each Date.Year([Дата]), 
      Int64.Type
   ), 
   tab_add_col_2 = Table.AddColumn(
      tab_add_col_1, 
      "Месяц", 
      each Date.Month([Дата]), 
      Int64.Type
   ), 
   tab_add_col_3 = Table.AddColumn(
      tab_add_col_2, 
      "День", 
      each Date.Day([Дата]), 
      Int64.Type
   ), 
   tab_add_col_4 = Table.AddColumn(
      tab_add_col_3, 
      "Квартал", 
      each Date.QuarterOfYear([Дата]), 
      Int64.Type
   ), 
   tab_add_col_5 = Table.AddColumn(
      tab_add_col_4, 
      "Неделя года", 
      each Date.WeekOfYear([Дата], Day.Monday), 
      Int64.Type
   ), 
   tab_add_col_6 = Table.AddColumn(
      tab_add_col_5, 
      "День недели", 
      each Date.DayOfWeek([Дата], Day.Monday) + 1, 
      Int64.Type
   )
in
   tab_add_col_6

Этот урок входит в Практический курс Power Query

Номер урока	Урок	Описание
1	Обработка типичной выписки	В этом уроке мы обработаем типичную банковскую выписку.
2	Интересная консолидация	В этом уроке мы будем практиковать объединение таблиц по вертикали.
3	Множественная консолидация при несовпадении заголовков	Объединим таблицы по вертикали, когда заголовки таблиц не совпадают, но порядок всегда одинаков.
4	Таблица дат	Для построения отчетов в модели данных Excel вам нужно создать таблицу дат. Сейчас вы узнаете как это сделать в Power Query.
5	Продажи год назад	В этом уроке мы узнаем как в Power Query получить таблицу с продажами прошлого года напротив текущих.
6	Консолидация книг и листов одновременно	В этом уроке мы научимся объединять все листы всех книг, т. е. выполним двухуровневую консолидацию.
7	Столбец общей суммы в PQ и PP	В этом уроке мы создадим столбец, в котором будет находиться общая сумма всех строк таблицы. Так же разберем эту же операцию в Power Pivot.
8	Скученные данные 3	В этом уроке мы обработаем еще один файл со скученными данными. На этот раз в одном столбце находятся даты и номенклатура. Нужно разбить этот столбец на два.
9	Строки преобразовать в столбцы	У нас есть таблица, в которой один столбец имеет скученные данные. Нам нужно скученный столбец преобразовать в столбцы.
10	Пивот, анпивот, группировка	В этом уроке мы попрактикуем анпивот, условную логику и группировку.
11	Нужные столбцы с нужного листа	В этом уроке мы научимся извлекать нужные столбцы с нужного листа не используя название самого листа.
12	Объединение, группировка, транспонирование, анпивот	В этом уроке на очень интересном примере из реальной жизни попрактикуем несколько техник Power Query: группировка, анпивот, объединение таблиц по горизонтали, транспонирование.
13	Нарастающий итог 4, много группировок	В этом уроке мы изучим еще 1 способ создать столбец нарастающего итога в Power Query. В отличие от других способов здесь мы не будем пользоваться формулами. Все сделаем при помощи пользовательского интерфейса.
14	Количество позиций в строке	В этом уроке мы посчитаем количество наименований, перечисленных в одной ячейке.
15	List Contains, Пользовательская функция	В этом уроке повторим создание пользовательских функций и рассмотрим еще 1 пример использования функции List.Contains.
16	ABC анализ	Научимся выполнять ABC анализ в Power Query.