Power Query. Ошибки. Плохо структурированный TXT

---

Описание

Если вы попробуйте подключиться к файлу TXT, который приложен к данному уроку, то увидите, что Power Query не смог самостоятельно разделить данные по столбцам.

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

Посмотрите на строку с порядковым номер 8648. Power Query не смог правильно прочитать 70 строк после этой и запихнул все 70 строк в одну ячейку.

Также в это TXT файле есть строки, в которых данные из столбца с названием сливаются с ценой, т. е. между ними вообще нет никакого разделителя.

Решение

Чтобы решить эту задачу нам нужно самим немного поизучать TXT-файл источник, чтобы найти закономерность. Лично я рекомендую скопировать часть данных в Word, чтобы было проще считать количество символов.

При помощи Word я выявил закономерность достаточно быстро. В последнем столбце всегда 21 символ. Теперь мы знаем, что делить данные по столбцам нам нужно с конца.

Дальше все просто. Смотрите видео для подробного разбора.

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

• Table.FromColumns
• Lines.FromBinary
• File.Contents
• Table.Skip
• Table.SplitColumn
• Splitter.SplitTextByPositions
• Table.TransformColumnTypes
• Splitter.SplitTextByEachDelimiter
• QuoteStyle.Csv
• Table.TransformColumns
• Text.Trim
• Int64.Type

Код

let
   path = Excel.CurrentWorkbook(){[Name = "Путь"]}[Content]{0}[Column1],
   source = Table.FromColumns(
      {
         Lines.FromBinary(
            File.Contents(path & "Источник.txt"),
            null,
            null,
            1251
         )
      }
   ),
   table_skip = Table.Skip(source, 1),
   col_split_1 = Table.SplitColumn(
      table_skip,
      "Column1",
      Splitter.SplitTextByPositions({0, 21}, true),
      {"Column1.1", "Цена"}
   ),
   types = Table.TransformColumnTypes(
      col_split_1,
      {{"Column1.1", type text}, {"Цена", type text}}
   ),
   col_split_2 = Table.SplitColumn(
      types,
      "Column1.1",
      Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),
      {"Порядковый номер", "Column1.1.2"}
   ),
   col_trim = Table.TransformColumns(
      col_split_2,
      {{"Column1.1.2", Text.Trim, type text}}
   ),
   col_split_3 = Table.SplitColumn(
      col_trim,
      "Column1.1.2",
      Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),
      {"Артикул", "Column1.1.2.2"}
   ),
   col_split_4 = Table.SplitColumn(
      col_split_3,
      "Column1.1.2.2",
      Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),
      {"Код марки", "Название"}
   ),
   col_trim_2 = Table.TransformColumns(
      col_split_4,
      {{"Название", Text.Trim, type text}}
   ),
   types_2 = Table.TransformColumnTypes(
      col_trim_2,
      {{"Цена", type number}},
      "en-001"
   ),
   types_3 = Table.TransformColumnTypes(
      types_2,
      {{"Порядковый номер", Int64.Type}}
   )
in
   types_3