Power Query. Подключиться к ZIP архиву

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

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

• YouTube
• VK

Ссылки:

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

Описание

В этом уроке мы воспользуемся функцией для подключения к файлам в ZIP архиве, которую написал KenR.

Мы подключимся к файлу TXT, находящемуся в ZIP архиве на вашей локальной машине, а потом сделаем то же самое, но файл будет находится в сети интернет.

Решение

Для подключения к ZIP файлу нам понадобится функция DecompressFiles, которую написал KenR.

После того как мы применим эту функцию останется только выполнить нужные вам преобразования

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

• BinaryFormat.Record
• BinaryFormat.Binary
• BinaryFormat.ByteOrder
• BinaryFormat.UnsignedInteger32
• ByteOrder.LittleEndian
• BinaryFormat.UnsignedInteger16
• Binary.Decompress
• Compression.Deflate
• Table.FromRecords
• Table.Combine
• Excel.CurrentWorkbook
• File.Contents
• Csv.Document
• Table.PromoteHeaders
• Table.SelectRows
• Table.SelectColumns
• Table.TransformColumnTypes
• Table.RenameColumns
• Table.Sort
• Order.Ascending
• Web.Contents

Код

Код функции Кена Р для разархивирования zip файла:

(ZIPFile, Position, FileToExtract, DataSoFar) =>
  let
    MyBinaryFormat = try
      BinaryFormat.Record(
        [
          DataToSkip = BinaryFormat.Binary(Position),
          MiscHeader = BinaryFormat.Binary(18),
          FileSize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
          UnCompressedFileSize = BinaryFormat.Binary(4),
          FileNameLen = BinaryFormat.ByteOrder(
            BinaryFormat.UnsignedInteger16,
            ByteOrder.LittleEndian
          ),
          ExtrasLen = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
          TheRest = BinaryFormat.Binary()
        ]
      )
    otherwise
      null,
    MyCompressedFileSize = try MyBinaryFormat(ZIPFile)[FileSize] + 1 otherwise null,
    MyFileNameLen = try MyBinaryFormat(ZIPFile)[FileNameLen] otherwise null,
    MyExtrasLen = try MyBinaryFormat(ZIPFile)[ExtrasLen] otherwise null,
    MyBinaryFormat2 = try
      BinaryFormat.Record(
        [
          DataToSkip = BinaryFormat.Binary(Position),
          Header     = BinaryFormat.Binary(30),
          Filename   = BinaryFormat.Text(MyFileNameLen),
          Extras     = BinaryFormat.Binary(MyExtrasLen),
          Data       = BinaryFormat.Binary(MyCompressedFileSize),
          TheRest    = BinaryFormat.Binary()
        ]
      )
    otherwise
      null,
    MyFileName = try MyBinaryFormat2(ZIPFile)[Filename] otherwise null,
    GetDataToDecompress = try MyBinaryFormat2(ZIPFile)[Data] otherwise null,
    DecompressData = try Binary.Decompress(GetDataToDecompress, Compression.Deflate) otherwise null,
    NewPosition = try
      Position + 30 + MyFileNameLen + MyExtrasLen + MyCompressedFileSize - 1
    otherwise
      null,
    AsATable = Table.FromRecords({[Filename = MyFileName, Content = DecompressData]}),
    #"Appended Query" = 
      if DecompressData = null then
        DataSoFar
      else if (MyFileName = FileToExtract) then
        AsATable
      else if (FileToExtract = "") and Position <> 0 then
        Table.Combine({DataSoFar, AsATable})
      else
        AsATable
  in
    if (MyFileName = FileToExtract) or (#"Appended Query" = DataSoFar) then
      #"Appended Query"
    else
      @fn_DecompressFiles(ZIPFile, NewPosition, FileToExtract, #"Appended Query")

Подключаемся к zip файлу, применяем функцию DecompressFiles и выполняем оставшиеся преобразования:

let
  path = Excel.CurrentWorkbook(){[Name = "path_zip"]}[Content]{0}[Column1],
  source = File.Contents(path & "\fut_fin_txt_2020.zip"),
  decompress = fn_DecompressFiles(source, 0, "", null),
  content = decompress{0}[Content],
  import_data = Csv.Document(
    content,
    [Delimiter = ",", Columns = 191, Encoding = 1251, QuoteStyle = QuoteStyle.None]
  ),
  headers = Table.PromoteHeaders(import_data, [PromoteAllScalars = true]),
  rows_select = Table.SelectRows(
    headers,
    each ([Market_and_Exchange_Names] = "RUSSIAN RUBLE - CHICAGO MERCANTILE EXCHANGE")
  ),
  cols_select = Table.SelectColumns(
    rows_select,
    {
      "Market_and_Exchange_Names",
      "Report_Date_as_YYYY-MM-DD",
      "Dealer_Positions_Long_All",
      "Dealer_Positions_Short_All",
      "Asset_Mgr_Positions_Long_All",
      "Asset_Mgr_Positions_Short_All"
    }
  ),
  col_types = Table.TransformColumnTypes(
    cols_select,
    {
      {"Report_Date_as_YYYY-MM-DD", type date},
      {"Dealer_Positions_Long_All", Int64.Type},
      {"Dealer_Positions_Short_All", Int64.Type},
      {"Asset_Mgr_Positions_Long_All", Int64.Type},
      {"Asset_Mgr_Positions_Short_All", Int64.Type}
    }
  ),
  cols_rename = Table.RenameColumns(
    col_types,
    {{"Market_and_Exchange_Names", "Product"}, {"Report_Date_as_YYYY-MM-DD", "Date"}}
  ),
  rows_sort = Table.Sort(cols_rename, {{"Date", Order.Ascending}})
in
  rows_sort

Как подключиться к zip архиву, который находится в интернете:

let
  path = Excel.CurrentWorkbook(){[Name = "path_url"]}[Content]{0}[Column1],
  source = Web.Contents(path),
  decompress = fn_DecompressFiles(source, 0, "", null),
  content = decompress{0}[Content],
  import_data = Csv.Document(
    content,
    [Delimiter = ",", Columns = 191, Encoding = 1251, QuoteStyle = QuoteStyle.None]
  ),
  headers = Table.PromoteHeaders(import_data, [PromoteAllScalars = true]),
  rows_select = Table.SelectRows(
    headers,
    each ([Market_and_Exchange_Names] = "RUSSIAN RUBLE - CHICAGO MERCANTILE EXCHANGE")
  ),
  cols_select = Table.SelectColumns(
    rows_select,
    {
      "Market_and_Exchange_Names",
      "Report_Date_as_YYYY-MM-DD",
      "Dealer_Positions_Long_All",
      "Dealer_Positions_Short_All",
      "Asset_Mgr_Positions_Long_All",
      "Asset_Mgr_Positions_Short_All"
    }
  ),
  col_types = Table.TransformColumnTypes(
    cols_select,
    {
      {"Report_Date_as_YYYY-MM-DD", type date},
      {"Dealer_Positions_Long_All", Int64.Type},
      {"Dealer_Positions_Short_All", Int64.Type},
      {"Asset_Mgr_Positions_Long_All", Int64.Type},
      {"Asset_Mgr_Positions_Short_All", Int64.Type}
    }
  ),
  cols_rename = Table.RenameColumns(
    col_types,
    {{"Market_and_Exchange_Names", "Product"}, {"Report_Date_as_YYYY-MM-DD", "Date"}}
  ),
  rows_sort = Table.Sort(cols_rename, {{"Date", Order.Ascending}})
in
  rows_sort

Power Query разное

Номер урока	Урок	Описание
1	Power Query. Знакомство с Power Query	В этом уроке мы познакомимся в Power Query. Зачем нужен Power Query Как установить Power Query Как его Настроить Как изменить запрос
2	Power Query. Подключение XML	В этом уроке мы научимся подключаться к файлам в формате XML и импортировать эти данные в Excel.
3	Power Query. Уникальные значения двух столбцов	В этом уроке мы получим уникальные значения из двух столбцов таблицы.
4	Power Query. Импорт таблиц PDF	Импорт таблиц из файла PDF, импорт таблиц из множества PDF файлов с объединением в один датасет.
5	Power Query. Собрать разбитую строку	В этом практическом уроке мы научимся соединять разбитую строку. Этот пример взят из реальной практики одного из спонсоров канала.
6	Power Query. Пивот со счетом	В этом уроке мы создадим пивот, в котором будут пронумерованы столбцы.
7	Power Query. Минимальное значение в диапазоне	В этом уроке мы найдем минимальное значение в диапазоне строк.
8	Power Query. Нарастающий итог 2	В этом уроке мы изучим еще один способ сделать нарастающий итог в Power Query.
9	Power Query. Нарастающий итог 3	В этом уроке мы разберем еще один способ выполнить нарастающий итог в Power Query.
10	Power Query. Прирост населения Китая	В этом уроке мы сравним прирост населения Китая с приростом населения мира в целом за последние 200 лет.
11	Power Query. Повторяющиеся значения в строке	В этом уроке разберем как определить есть ли в строке повторения.
12	Power Query. Таблица навигации по функциям М	В этом уроке вы узнаете как создать таблицу навигации по всем функциям языка Power Query.
13	Power Query. Удалить запросы и модель данных из книги	Разберем как быстро удалить все запросы и модель данных из текущей книги.
14	Power Query. Открыть еще 1 Excel и еще 3 трюка	В этом видео я покажу как открыть еще 1 файл Excel, если у вас уже запущен Power Query.
15	Power Query. Подключиться к ZIP архиву	Пользовательская функция для подключения к zip файлу. Подключимся к txt файлу, который находится в zip архиве.
16	Power Query. Импорт Word	Импортируем таблицу из документа Word. Для спонсоров разберем импорт таблицы с объединенными ячейками.
17	Power Query. Фильтрация списком	В этом уроке мы хотим отфильтровать таблицу при помощи списка, например, хотим получить продажи определенных товаров.
18	Power Query. Пользовательская функция Switch	В этом уроке мы создадим пользовательскую функцию Switch.
19	Power Query. Информация о формате, Чтение zip	В этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query.
20	Power Query. Импорт данных из gz	В этом уроке мы разберем как импортировать файл в формате gz.
21	Power Query. Удалить лишние пробелы, Text.Split	В этом уроке мы научимся удалять лишние пробелы в текстовом столбце таблицы.
22	Power Query. Параметры в SQL-запросе	Вы хотите, чтобы в ваш SQL-запрос подставлялось значение из параметра, источником которого является ячейка с листа Excel.
23	Power Query. Параметры в SQL-запросе 2	Ваш запрос очень большой и количество параметров в нем большое. Как организовать все так, чтобы было удобно работать.
24	Power Query. Добавить столбец в каждую таблицу табличного столбца	В этом уроке вы узнаете как трансформировать табличный столбец, например, вы сможете добавить столбец индекса внутрь каждой таблицы табличного столбца.
25	Power Query. Интервальный просмотр 1 (ВПР 1)	Объединить 2 таблицы с интервальным просмотром = 1.
26	Power Query. Относительный путь к файлу и папке	Если ваш источник находится в той же папке, что и отчет, то вы можете указать относительный путь. В таком случае подключение не будет ломаться, если вы запустите файл на другом компьютере.
27	Power Query. Нарастающий итог в каждой категории	Применим функцию нарастающего итога не ко всей таблице, а к определенному окну.
28	Power Query. ВПР без Merge или Join	Вам нужно подставить данные из столбца другой таблицы. Как это сделать без объединения таблиц.