Power Query Базовый №3. Импорт csv, txt, таблиц, диапазонов

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

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

• YouTube
• VK

Ссылки:

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

Описание

В этом уроке мы разберем следующие вопросы:

• Как подключаться к CSV файлам в Power Query
• Как подключаться к TXT файлам в Power Query
• Как подключаться к таблице Excel в Power Query
• Как подключаться к диапазону Excel в Power Query
• Как создать динамический диапазон в Excel
• Как подключаться к динамическому диапазону Excel в Power Query

Решение

Подключение csv/txt в Power Query

Для подключения csv/txt файла нужно указать источинк «Текстовый или csv файл». Файлы csv — это текстовые файлы с разделителями запятыми.

После подключения к приложенному txt файлу вы увидите в строке форму сгенерированную автоматически формулу:

Csv.Document(
    File.Contents(Параметр&"И_Время.txt"),
    [
        Delimiter="#(tab)",
        Columns=8,
        Encoding=1251,
        QuoteStyle=QuoteStyle.None
    ]
)

Давайте разберем эту формулу:

Csv.Document — эта функция используется для подключения к файлам csv и txt. Далее в скобках мы видим параметры этой функции;

File.Contents(Параметр&»И_Время.txt») — функция File.Contents возвращает содержимое нужного нам файла. В скобках находится само название файла. Т. к. имя файла мы получаем из параметра, находящегося в другом запросе, то вместо полного пути мы просто видим слово «Параметр». Далее следует перечисление параметров для чтения файла txt/csv;

Delimiter — параметр указывает, каким разделителем разделены ячейки. В данном случае используются табы (знаки табуляции);

Columns — количество столбцов;

Encoding — используемая кодировка;

QuoteStyle — показывает как нужно читать разрывы строк.

Подключение к таблицам/диапазонам Excel

Power Query может подключаться к таблицам Excel, именным диапазонам Excel и областям печати.

В приложенном файле разобрано 3 подключения: к таблице, именному диапазону и динамическому именному диапазону.

В Power Query, чтобы подключиться к таблице/именному диапазону текущей книги мы всегда пользуемся функцией Excel.CurrentWorkbook.

Если мы подключимся к таблице Excel, то она будет автоматически расширяться при добавлении в нее новых данных, но именной диапазон автоматически расширяться не будет.

На листе «ДинамическийДиапазон» находится динамический именной диапазон, который задан при помощи формулы:

ДинамическийДиапазон!$A$1:ИНДЕКС(
    ДинамическийДиапазон!$J:$J;
    ПОИСКПОЗ(
        99^99;
        ДинамическийДиапазон!$A:$A;1
    )
)

Эта формула нужна, чтобы именной диапазон расширялся вместе с добавлением в него новых данных.

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

• Csv.Document
• File.Contents
• QuoteStyle.None
• Table.PromoteHeaders
• Table.TransformColumnTypes
• Table.SelectRows
• #datetime
• Table.ReorderColumns
• Excel.CurrentWorkbook
• Table.Skip

Код

Код импорта CSV из видео:

let
   source = Csv.Document(
      File.Contents(Путь & "И_CRM.csv"),
      [Delimiter = ";", Columns = 12, Encoding = 65001, QuoteStyle = QuoteStyle.None]
   ),
   promote_headers = Table.PromoteHeaders(source, [PromoteAllScalars = true]),
   types = Table.TransformColumnTypes(
      promote_headers,
      {{"Дата доставки", type date}, {"Дата и время", type datetime}}
   ),
   rows_select = Table.SelectRows(
      types,
      each [Дата и время]
         >= #datetime(2018, 11, 1, 0, 0, 0) and [Дата и время]
         <= #datetime(2018, 11, 30, 0, 0, 0)
   ),
   cols_reorder = Table.ReorderColumns(
      rows_select,
      {
         "Дата и время",
         "Контактный телефон",
         "E-mail",
         "Сумма",
         "Время доставки",
         "Метро",
         "Дата доставки",
         "Номер",
         "Покупатель",
         "Менеджер",
         "Статус заказа",
         "Состав"
      }
   )
in
   cols_reorder

Код импорта из таблицы Excel из видео:

let
   source = Excel.CurrentWorkbook(),
   get_table = source{[Name = "Таблица"]}[Content],
   types = Table.TransformColumnTypes(
      get_table,
      {
         {"Дата", type date},
         {"Чек", type text},
         {"Код", type text},
         {"Продавец", type text},
         {"Группа", type text},
         {"Канал", type text},
         {"Кол-во", Int64.Type},
         {"Себестоимость", type number},
         {"Сумма", type number},
         {"Прибыль", type number}
      }
   )
in
   types

Код импорта из диапазона Excel из видео:

let
   source = Excel.CurrentWorkbook(),
   get_table = source{[Name = "Диапазон"]}[Content],
   promote_headers = Table.PromoteHeaders(get_table, [PromoteAllScalars = true]),
   types = Table.TransformColumnTypes(
      promote_headers,
      {
         {"Дата", type date},
         {"Чек", type text},
         {"Код", type text},
         {"Продавец", type text},
         {"Группа", type text},
         {"Канал", type text},
         {"Кол-во", Int64.Type},
         {"Себестоимость", type number},
         {"Сумма", type number},
         {"Прибыль", type number}
      }
   )
in
   types

Код импорта из TXT файла из видео:

let
   source = Csv.Document(
      File.Contents(Параметр & "И_Время.txt"),
      [
         Delimiter  = "#(tab)",
         Columns    = 8,
         Encoding   = 1251,
         QuoteStyle = QuoteStyle.None
      ]
   ),
   rows_skip = Table.Skip(source, 3),
   promote_headers = Table.PromoteHeaders(rows_skip, [PromoteAllScalars = true]),
   types = Table.TransformColumnTypes(
      promote_headers,
      {
         {"Дата", type date},
         {"Pos ID", Int64.Type},
         {"Начало работы", type time},
         {"Окончание работы", type time}
      }
   )
in
   types

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