Power Query Базовый №2. Подключение Excel

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

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

Все уроки Базового курса Power Query на этой странице или в этом плейлисте.

Описание

В этом уроке мы научимся подключаться к книге 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

Этот урок входит в Базовый курс 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	Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию.