Power Query. Основы. Подключение Excel

---

Описание

В этом уроке мы научимся подключаться к книге Excel. Помимо самого подключения научимся делать следующее:

• Указание типов данных столбцов
• Указание типов данных с использованием локали (тип данных с языком)
• Добавим столбец, который извлечет текст перед разделителем — аналог функции ЛЕВСИМВ в сочетании с НАЙТИ
• Добавим столбец, который извлечет последние 2 символа — аналог функции ПРАВСИМВ
• Добавим столбец, который извлечет текст между разделителями — аналог функции ПСТР
• Разделим столбец по разделителю — аналог операции текст по столбцам из стандартного функционала Excel

Решение

Подготовка к подключение к Excel-книге в Power Query

• Скачайте все приложенные файлы
• Откройте видео к уроку
• Создайте новую книгу

Подключение к Excel в Power Query

1. В новой книге подключитесь к файлу. Данные — Получить данные — Из файла — Из книги

2. Укажите скачанный файл с данными

3. Удалите все шаги кроме шага Источник

4.. Кликните по ссылке Table, чтобы развернуть таблицу

5. Поднимите заголовки. Главная — Использовать первую строку в качестве заголовков

6. Укажите типы данных для текстовых столбцов

7. Для числовых столбцов измените тип данных с использованием локали

Теперь пора разделить столбец Product Number. Сначала извлечем первые 2 символа. Эти 2 буквы указывают на категорию товара.

8.. Кликаем на столбец Product Number — Извлечь — Текст перед разделителем — Указываем тире

Теперь извлечем последние символы после тире. Эти символы указывают на цвет товара.

9. Кликаем на столбец Product Number — Извлечь — Текст после разделителя — Указать тире — Нажать расширенные настройки — Выбрать опцию От конца входных данных

Теперь извлечем текст между вторым и третьим тире. Эти символы указывают на размер.

10. Кликаем на столбец Product Name — Извлечь — Текст между разделителями — Указать тире в обоих случаях — В расширенных настройках указать одно пропускаемое тире от начала

Второй способ деления столбца

Скопируйте запрос, который мы создали ранее. Удалите 3 последних шага, т.е. те, где мы делили столбец Product Number.

Теперь разделим его другим способом.

Кликаем на столбец Product Number — Вкладка Преобразование — Разделить столбец — По разделителю — Указать тире и выбрать опцию По каждому вхождению разделителя

Получится 4 столбца. Оставляем только 3 из них: категория, цвет, размер. Лишний столбец удаляем. Готово.

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

• Excel.CurrentWorkbook
• Excel.Workbook
• File.Contents
• Table.PromoteHeaders
• Table.TransformColumnTypes
• Table.AddColumn
• Text.BeforeDelimiter
• Text.AfterDelimiter
• Text.BetweenDelimiters
• Table.SplitColumn
• Table.RemoveColumns

Код

Код с первым способом деления строки:

let
   file = Excel.CurrentWorkbook(){[Name = "file"]}[Content]{0}[Column1],

   source = Excel.Workbook(File.Contents(file)),

   get_table = source{[Item = "Лист1", Kind = "Sheet"]}[Data],

   promote_headers = Table.PromoteHeaders(get_table, [PromoteAllScalars = true]),

   types = Table.TransformColumnTypes(
      promote_headers,
      {
         {"ProductID", type text},
         {"Product", type text},
         {"Product Number", type text}
      }
   ),

   types_2 = Table.TransformColumnTypes(
      types,
      {{"Cost", type number}, {"Price", type number}},
      "en-US"
   ),

   col_category = Table.AddColumn(
      types_2,
      "Категория",
      each Text.BeforeDelimiter([Product Number], "-"),
      type text
   ),

   col_color = Table.AddColumn(
      col_category,
      "Цвет",
      each Text.AfterDelimiter([Product Number], "-", 2),
      type text
   ),

   col_size = Table.AddColumn(
      col_color,
      "Размер",
      each Text.BetweenDelimiters([Product Number], "-", "-", 1, 0),
      type text
   )
   
in
   col_size

Код со вторым способом деления строки:

let
   file = Excel.CurrentWorkbook(){[Name = "file"]}[Content]{0}[Column1],

   source = Excel.Workbook(File.Contents(file)),

   get_table = source{[Item = "Лист1", Kind = "Sheet"]}[Data],

   promote_headers = Table.PromoteHeaders(get_table, [PromoteAllScalars = true]),

   types_1 = Table.TransformColumnTypes(
      promote_headers,
      {
         {"ProductID", type text},
         {"Product", type text},
         {"Product Number", type text}
      }
   ),

   types_2 = Table.TransformColumnTypes(
      types_1,
      {{"Cost", type number}, {"Price", type number}},
      "en-US"
   ),
   
   col_split = Table.SplitColumn(
      types_2,
      "Product Number",
      Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
      {"Категория", "Product Number.2", "Размер", "Цвет"}
   ),

   types_3 = Table.TransformColumnTypes(
      col_split,
      {
         {"Категория", type text},
         {"Product Number.2", type text},
         {"Размер", type text},
         {"Цвет", type text}
      }
   ),

   remove_col = Table.RemoveColumns(types_3, {"Product Number.2"})
   
in
   remove_col