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