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