Файлы к уроку:
Все уроки курса Работа с ошибками в Power Query на этой странице и в этом плейлисте.
Описание
Если вы попробуйте подключиться к файлу 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Этот урок входит в курс Работа с ошибками в Power Query
| Номер урока | Урок | Описание |
|---|---|---|
| 1 | Power Query Ошибки №1. Измененный тип, Неверная фильтрация в UI | Почему возникает ошибка Expression.Error Столбец таблицы не найден? Одна из причин — шаг Измененный тип. Почему в вашей итоговой таблицы не все данные, которые должны там оказаться? Вероятно вы совершили ошибку неверной фильтрации в пользовательском интерфейсе. |
| 2 | Power Query Ошибки №2. Фиксированная ширина столбца Excel | В этом уроке вы узнаете как отключить автоматическое определение ширины столбцов таблицы Excel при обновлении запроса Power Query. |
| 3 | Power Query Ошибки №3. Formula Firewall, запрос ссылается на… | Вы работаете в Power Query, но при попытке обновить все возникает ошибка Formula Firewall, запрос ссылается на… Что делать? Как исправить? |
| 4 | Power Query Ошибки №4. Доступ к веб-содержимому | В этом уроке вы узнаете как избавиться от надоедливого окна доступ к веб-содержимому. |
| 5 | Power Query Ошибки №5. Количество столбцов CSV | При импорте CSV получается неверное количество столбцов. |
| 6 | Power Query Ошибки №6. Не получается изменить тип данных (DataFormat.Error) | При попытке изменить тип данных с текстового на дату возникает ошибка DataFormat.Error. |
| 7 | Power Query Ошибки №7. Разные имена листов | В этом уроке мы решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами. |
| 8 | Power Query Ошибки №8. Разные имена листов 2 | В этом уроке мы другими способами решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами. |
| 9 | Power Query Ошибки №9. Разные имена столбцов, Table.TransformColumnNames | В этом уроке мы разберем проблему, когда нам присылают файлы, в которых всегда столбцы названы по-разному. |
| 10 | Power Query Ошибки №10. Как развернуть все столбцы | В этой задаче мы научимся разворачивать все столбцы табличного столбца не перечисляя каждый из них хардкодом. |
| 11 | Power Query Ошибки №11. Подключиться к последнему файлу | В этом уроке мы научимся подключаться к самому свежему файлу из нужной нам папки с нужным названием. |
| 12 | Power Query Ошибки №12. Консолидация и MissingField.Type | Бывает вы ходите выполнить добавление таблиц друг под другом с предварительной обработкой, но получаете ошибку Expression.Error: Столбец таблицы не найден. |
| 13 | Power Query Ошибки №13. Удалить пустые столбцы | Как без хардкода автоматически удалить пустые столбцы в Power Query, которые есть в импортируемой таблицы. Разберем 2 способа. Один из них прост до безобразия, а для второго понадобится функция Table.Profile. |
| 14 | Power Query Ошибки №14. Удалить лишние пробелы | В этом уроке мы разберем еще 1 способ удалить лишние пробелы в текстовом столбце Power Query. Для этого мы повторим и изучим несколько новых функций. Text.SplitAny List.Select Character.FromNumber Text.Combine |
| 15 | Power Query Ошибки №15. Плохо структурированный TXT | Как быть, если текстовый файл разбивается на столбцы неправильно? |
| 16 | Power Query Ошибки №16. При округлении не совпадает общая сумма | Разберем ошибку, которая возникает при необходимости разбить число на определенные доли, а результаты округлить до двух знаков после запятой. В такой ситуации может получиться, что сумма слагаемых не будет равняться исходному числу. |
| 17 | Power Query Ошибки №17. Удаление дубликатов, Подводные камни Table. Distinct | Что может пойти не так при использовании функции удаления дубликатов. |
| 18 | Power Query Ошибки №18. Удаление дубликатов 2, Подводные камни Table. Distinct | Что может пойти не так при использовании функции удаления дубликатов. Другое решение данной проблемы. |
| 19 | Power Query Ошибки №19. Неверная разбивка на столбцы | При импорте данных из CSV или TXT разбивка на столбцы происходит неправильно. |
| 20 | Power Query Ошибки №20. Пустая таблица при импорте Excel (XLSX, XLS) | Вы пытаетесь подключиться к книге Excel, но импортируется пустая таблица. |
