Power Query Базовый №1. Зачем нужен Power Query, Основные возможности

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

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

• YouTube
• VK

Ссылки:

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

Основные возможности

Основные возможности Power Query:

• Импорт данных
  • Excel
  • CSV/TXT
  • Интернет
  • Базы данных
• Очистка
  • Фильтрация строк/столбцов
  • Объединить/разделить столбец
  • Извлечь часть строки/даты
  • Добавление столбцов
• Объединение таблиц
  • По вертикали
  • По горизонтали
• Изменить форму
  • Пивот
  • Анпивот
  • Транспонирование
• Вычисления
  • Агрегирование
  • Вычисляемые столбцы
• Прочее
  • Генерирование серий
  • Создание пользовательских функций
  • …

Импорт данных

Импорт из Excel

Для подключение к файлу Excel мы в ленте Excel можем перейти на вкладку Данные — Получить данные — Excel.

Пример кода подключения к Excel:

let
   source = Excel.Workbook(
      File.Contents(
         filepath
      ),
      null,
      true
   ),
   get_table = source{[Item = "Таблица", Kind = "Table"]}[Data],
   set_types = Table.TransformColumnTypes(
      get_table,
      {
         {"Дата", type date},
         {"Кол-во", Int64.Type},
         {"Себестоимость", type number},
         {"Сумма", type number},
         {"Прибыль", type number}
      }
   )
in
   set_types

Импорт из CSV

Подключение к CSV из пользовательского интерфейса Excel или Power Query выполняется абсолютно так же, как и подключение к Excel.

Пример кода для подключения к CSV:

let
   source = Csv.Document(
      File.Contents(
         filepath
      ),
      [Delimiter = ";", Columns = 12, Encoding = 65001, QuoteStyle = QuoteStyle.None]
   ),
   headers = Table.PromoteHeaders(source, [PromoteAllScalars = true]),
   set_types_1 = Table.TransformColumnTypes(
      headers,
      {{"Сумма", type number}},
      "en-001"
   ),
   set_types_2 = Table.TransformColumnTypes(
      set_types_1,
      {{"Дата доставки", type date}, {"Дата и время", type datetime}}
   )
in
   set_types_2

Импорт из Интернета

Чтобы импортировать таблицу из интернета нужно перейти на вкладку Данные — Получить данные — Из интернета — Вставить url.

Пример кода для получения таблицы с web-страницы:

let
   source = Web.Page(Web.Contents("https://bigmacindex.ru/2020-07")),
   get_table = source{0}[Data],
   set_types_1 = Table.TransformColumnTypes(
      get_table,
      {{"Местная валюта", type number}},
      "en-001"
   ),
   set_types_2 = Table.TransformColumnTypes(set_types_1, {{"Рубли ↓↑", Int64.Type}})
in
   set_types_2

Очистка

В этой части урока мы сделали базовую очистку данных. Код:

let
   path = Excel.CurrentWorkbook(){[Name = "Путь"]}[Content]{0}[Column1],
   source = Csv.Document(
      File.Contents(path),
      [Delimiter = "	", Columns = 24, Encoding = 65001, QuoteStyle = QuoteStyle.None]
   ),
   rows_skip_1 = Table.Skip(source, 4),
   rows_skip_2 = Table.RemoveLastN(rows_skip_1, 1),
   headers = Table.PromoteHeaders(rows_skip_2, [PromoteAllScalars = true]),
   select_rows = Table.SelectRows(headers, each ([Вид операции] = "Продажа")),
   select_cols = Table.SelectColumns(
      select_rows,
      {
         "Дата",
         "Документ",
         "PosID",
         "Вид операции",
         "Код",
         "Количество",
         "Сумма",
         "Себестоимость сумма",
         "Премия сумма"
      }
   ),
   split_cols = Table.SplitColumn(
      select_cols,
      "Документ",
      Splitter.SplitTextByEachDelimiter({" от "}, QuoteStyle.Csv, false),
      {"Чек", "Дата и время"}
   ),
   transform_cols = Table.TransformColumns(
      split_cols,
      {{"Дата и время", each Text.AfterDelimiter(_, " "), type text}}
   ),
   transform_types = Table.TransformColumnTypes(
      transform_cols,
      {
         {"Дата", type date},
         {"PosID", Int64.Type},
         {"Количество", Int64.Type},
         {"Сумма", type number},
         {"Себестоимость сумма", type number},
         {"Премия сумма", type number},
         {"Дата и время", type time}
      }
   ),
   add_col = Table.AddColumn(
      transform_types,
      "Прибыль",
      each List.Sum({[Сумма], - [Себестоимость сумма]}),
      Number.Type
   )
in
   add_col

Изменение формы

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

Решение

Сначала нужно найти закономерность в исходных данных. Мы можем обнаружить, что каждая строка содержит 9 значений.

Ключом к решению этой задачи является добавление столбца индекса. Если мы вычислим остаток от деления номера строки на 9, то сможем обнаружить начало каждой строки.

Код

let
   source = Excel.CurrentWorkbook(){[Name = "Сделки"]}[Content],
   index_col = Table.AddIndexColumn(source, "Индекс", 0, 1),
   mod_col = Table.AddColumn(
      index_col,
      "Остаток от деления",
      each Number.Mod([Индекс], 9),
      type number
   ),
   pivot = Table.Pivot(
      Table.TransformColumnTypes(
         mod_col,
         {{"Остаток от деления", type text}},
         "en-US"
      ),
      List.Distinct(
         Table.TransformColumnTypes(
            mod_col,
            {{"Остаток от деления", type text}},
            "en-US"
         )[#"Остаток от деления"]
      ),
      "Остаток от деления",
      "Сделки"
   ),
   fillup = Table.FillUp(pivot, {"1", "2", "3", "4", "5"}),
   filter_table = Table.SelectRows(fillup, each ([0] <> null)),
   select_cols = Table.SelectColumns(filter_table, {"0", "1", "2", "3", "4", "5"}),
   rename_cols = Table.RenameColumns(
      select_cols,
      {
         {"0", "Дата"},
         {"1", "Тикер"},
         {"2", "Сумма"},
         {"3", "Количество"},
         {"4", "Цена"},
         {"5", "Комиссия"}
      }
   ),
   set_types = Table.TransformColumnTypes(
      rename_cols,
      {
         {"Дата", type date},
         {"Тикер", type text},
         {"Сумма", type number},
         {"Количество", type number},
         {"Цена", type number},
         {"Комиссия", type number}
      }
   )
in
   set_types

Объединение запросов

Существует способа объединения таблиц:

• По горизонтали
• По вертикали

Объединение по горизонтали — это то же самое, что операция JOIN языка SQL.

Объединение по вертикали — это то же самое, что операция UNION ALL из SQL.

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

Код

let
   source = Table.Combine({ККМтовар1, ККМтовар2}),
   join = Table.NestedJoin(
      source,
      {"Код"},
      СправочникТоваров,
      {"Код"},
      "СправочникТоваров",
      JoinKind.LeftOuter
   ),
   expand = Table.ExpandTableColumn(
      join,
      "СправочникТоваров",
      {"Номенклатура1", "Номенклатура2", "Поставщик", "Бренд"},
      {"Номенклатура1", "Номенклатура2", "Поставщик", "Бренд"}
   ),
   set_types = Table.TransformColumnTypes(
      expand,
      {{"Дата", type date}, {"Месяц", type date}, {"POSID", Int64.Type}}
   )
in
   set_types

Вычисления

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

В этом примере мы выполним оба вида вычислений.

Код

let
   source = Excel.CurrentWorkbook(){[Name = "ККМтовар"]}[Content],
   set_types = Table.TransformColumnTypes(source, {{"Дата", type date}}),
   group_by = Table.Group(
      set_types,
      {"Категория"},
      {
         {"Сумма", each List.Sum([Сумма продаж]), type number},
         {"Прибыль", each List.Sum([#"Валовая прибыль, руб."]), type number}
      }
   ),
   sort = Table.Sort(group_by, {{"Категория", Order.Ascending}}),
   add_column = Table.AddColumn(
      sort,
      "Прибыльность",
      each [Прибыль] / [Сумма],
      Percentage.Type
   )
in
   add_column

Прочее

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

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

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

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

Код

Пользовательская функция:

(url) =>
   let
      source      = Web.Page(Web.Contents(url)),
      select_rows = Table.SelectRows(source, each ([ClassName] = "result_table")),
      Data        = select_rows{0}[Data]
   in
      Data

Генерируем серию и обрабатываем каждую ссылку:

let
   url_part = "https://www.calc.ru/kotirovka-dollar-ssha.html?date=",
   months = {
      "-01",
      "-02",
      "-03",
      "-04",
      "-05",
      "-06",
      "-07",
      "-08",
      "-09",
      "-10",
      "-11",
      "-12"
   },
   list_generate = List.Generate(
      () => [x = 1992],
      each [x] <= 2020,
      each [x = [x] + 1],
      each [year = [x], months = months, url_part = url_part]
   ),
   to_table = Table.FromList(
      list_generate,
      Splitter.SplitByNothing(),
      null,
      null,
      ExtraValues.Error
   ),
   expand_col = Table.ExpandRecordColumn(
      to_table,
      "Column1",
      {"year", "months", "url_part"},
      {"year", "months", "url_part"}
   ),
   expand_col_2 = Table.ExpandListColumn(expand_col, "months"),
   combine_cols = Table.CombineColumns(
      Table.TransformColumnTypes(expand_col_2, {{"year", type text}}, "ru-RU"),
      {"url_part", "year", "months"},
      Combiner.CombineTextByDelimiter("", QuoteStyle.None),
      "url"
   ),
   add_column = Table.AddColumn(
      combine_cols,
      "Пользовательский",
      each fn_get_usdrub([url])
   ),
   expand_col_3 = Table.ExpandTableColumn(
      add_column,
      "Пользовательский",
      {"Дата", "Курс"},
      {"Дата", "Курс"}
   ),
   select_rows = Table.SelectRows(expand_col_3, each Text.Length([Дата]) <> 4),
   set_types = Table.TransformColumnTypes(select_rows, {{"Дата", type date}}),
   set_types_locale = Table.TransformColumnTypes(
      set_types,
      {{"Курс", type number}},
      "en-001"
   ),
   replace_val = Table.ReplaceValue(
      set_types_locale,
      each [Курс],
      each if [Дата] < Date.From("30.12.1997") then [Курс] / 1000 else [Курс],
      Replacer.ReplaceValue,
      {"Курс"}
   )
in
   replace_val

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

• Combiner.CombineTextByDelimiter
• Csv.Document
• Date.From
• Excel.CurrentWorkbook
• Excel.Workbook
• ExtraValues.Error
• File.Contents
• JoinKind.LeftOuter
• List.Distinct
• List.Generate
• List.Sum
• Number.Mod
• Number.Type
• Percentage.Type
• QuoteStyle.Csv
• QuoteStyle.None
• Replacer.ReplaceValue
• Splitter.SplitByNothing
• Splitter.SplitTextByEachDelimiter
• Table.AddColumn
• Table.AddIndexColumn
• Table.Combine
• Table.CombineColumns
• Table.ExpandListColumn
• Table.ExpandRecordColumn
• Table.FillUp
• Table.FromList
• Table.Group
• Table.NestedJoin
• Table.Pivot
• Table.PromoteHeaders
• Table.RemoveLastN
• Table.RenameColumns
• Table.ReplaceValue
• Table.SelectColumns
• Table.SelectRows
• Table.Skip
• Table.Sort
• Table.SplitColumn
• Table.TransformColumns
• Table.TransformColumnTypes
• Text.AfterDelimiter
• Web.Contents
• Web.Page

Этот урок входит в Базовый курс Power Query

Номер урока	Урок	Описание
1	Зачем нужен Power Query. Обзор возможностей	Этот урок сам по себе является мини-курсом. Здесь вы узнаете для каких видов операций с данными создан Power Query.
2	Подключение Excel	Подключаемся к файлам Excel. Импортируем данные из таблиц, именных диапазонов, динамических именных диапазонов.
3	Подключение CSV/TXT, таблиц, диапазонов	Подключаемся к к файлам CSV/TXT, Excel.
4	Объединить таблицы по вертикали	Учимся объединять две таблицы по вертикали — combine.
5	Объединить по вертикали все таблицы одной книги друг за другом	Как объединить по вертикали все таблицы одной книги, находящиеся на разных листах Excel.
6	Объединить по вертикали все файлы в папке	Объединяем по вертикали таблицы, которые находятся в разных файлах в одной папке.
7	Объединение таблиц по горизонтали	Учимся объединять таблицы по горизонтали — JOIN, merge.
8	Объединить таблицы с агрегированием	Объединить таблицы по горизонтали и сразу выполнить группировку с агрегированием — JOIN + GROUP BY.
9	Анпивот (Unpivot)	Изучаем операцию Анпивот — из сводной таблицы делаем таблицу с данными.
10	Многоуровневый анпивот (Анпивот с подкатегориями)	Более сложный вариант Анпивота — в строках находится несколько измерений.
11	Скученные данные	Данные собраны в одном столбце, нужно правильно его разбить на несколько.
12	Скученные данные 2	Разбираем еще один пример скученных данных.
13	Ссылка на другую строку	Как сослаться на другую строку.
14	Ссылка на другую строку 2	Как сослаться на другую строку, используя объединение по горизонтали.
15	Виды объединения таблиц по горизонтали	Изучаем виды объединения таблиц по горизонтали — LEFT JOIN, FULL JOIN, INNER JOIN, CROSS JOIN.
16	Виды объединения таблиц по горизонтали 2	Изучаем анти-соединение и соединение таблицы с ней же самой — ANTI JOIN, SELF JOIN.
17	Группировка	Изучаем операцию группировки с агрегированием — GROUP BY.
18	Консолидация множества таблиц пользовательской функцией	Объединяем по вертикали множество таблиц с предварительной обработкой при помощи пользовательской функции.
19	Деление на справочник и факт	Разделим один датасет на два датасета: справочник и факт.
20	Создание параметра	Мы можем ввести значение в какую-то ячейку Excel, а потом передать это значение в формулу Power Query.
21	Таблица параметров	Создадим целую таблицу параметров и будем их использовать в запросах Power Query.
22	Объединение таблиц по вертикали, когда не совпадают заголовки столбцов	Как объединить две таблицы по вертикали, если названия столбцов не совпадают.
23	Поиск ключевых слов	Научимся искать ключевые слова в текстовом поле.
24	Поиск ключевых слов 2	Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию.