Страницы: 1
RSS
Разбивка ячейки на столбцы PQ
 
Друзья, привет!
Столкнулся с проблемой выгружаемого отчета, программа при выгрузке отчета сливает все данные в одну ячейку, что не дает возможности построить необходимые разрезы. Во всех строках единая структура и вроде есть символы для определения границ текста для распределения по столбцам. Думал сделать формулами, но на выходе более 600к строк из-за чего Excel загибается.

Гуру PQ можете помочь с обработкой по разложению данных с небольшим комментарием по реализации.
Пример исходных данных и пример результата во вложении.

Заранее благодарен.
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
phelex, задачка простая, решается так: превращаете таблицу в умную, Данные - из таблицы, на вкладке Главная - Разделить столбец по разделителю - запятая и т.д. Все решается мышкой. В файле показан пример как разделить первый нужный столбец "Покупатель". Столбец "Наименование продукции" предлагаю разделить самостоятельно для закрепления материала.
Изменено: mtts54 - 07.08.2020 15:39:14
Excel непознаваем как атом.
 
Цитата
phelex написал:
программа при выгрузке отчета сливает все данные в одну ячейку
Это прямо при выгрузке в экселевскую таблицу ваша программа так выгружает? Или все таки выгрузка идет в текст, а у вас просто не получается корректно это дело импортнуть в эксель? Если второе, то лучше приложите кусок текстового файла. Запрос будет по-проще.
Вот горшок пустой, он предмет простой...
 
mtts54,Названия столбцов не должны содержать "ИНН:"
PooHkrd, в таком виде выгружается, изменить никак.
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
phelex, подскажите, поле "Наименование продукции" - в самом наименовании возможны пробелы, небуквенные символы?
Цитата
mtts54 написал:
задачка простая,
ну-ну, продемонстрировали бы тогда на столбце "Наименование продукции"
Соблюдение правил форума не освобождает от модераторского произвола
 
Доброе время суток
Цитата
buchlotnik написал:
ну-ну, продемонстрировали бы тогда на столбце "Наименование продукции"
Михаил, я думаю, что mtts54 подразумевал простоту алгоритмического решения, а не его трудозатратность и нудность. В силу трудозатратности никто больше и не взялся решать - что делать, в общем-то понятно, но убивать на это время...
P. S. Ну, и как всегда с задачами подобного плана висит ожидание - ой, я вот такой момент упустил, а вот ещё такой вариант почему-то не обрабатывает :)
Изменено: Андрей VG - 08.08.2020 09:28:47
 
Андрей VG, приветствую, ну в софистику на тему "простоты" вдаваться не будем  ;)  
Первый столбец поделил (вспомнился хороший образчик кода):
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    lst = List.Transform(List.Select(List.Zip({Table.ColumnNames(from),Record.ToList(from{0})}),each _{1}<>null),each _{0}),
    lst1={"Наименование", "ИНН", "КПП", "Адрес"},
    select=Table.SelectColumns(from,lst),
    f=(t)=>Record.FromList( List.Accumulate(List.Transform(List.Skip(lst1,1),each ", "&_&": "),{t}, 
                                (x, y) =>[  a = List.Transform(x, each Text.Split(_, y)),
                                        b = List.Combine(a),
                                        c = List.Select(b, each _<>"" and _<>" ")][c]),lst1),
    tbl = Table.TransformColumns(select,{"Покупатель",f}),
    to = Table.ExpandRecordColumn(tbl, "Покупатель", lst1)
in
    to

по второму ждем
Цитата
Андрей VG написал:
ой, я вот такой момент упустил, а вот ещё такой вариант
Изменено: buchlotnik - 08.08.2020 10:06:05
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik,могут быть и пробелы и спецсимволы ковычки, % - есть единый признак для обрезки 000% и до первого пробела влево во всех обрезает название, остальные поля имеют единый формат: Производитель может иметь в названии двойные ковычки, тут  ИНН и КПП. формат единый по всем строкам признак / и движение влево до первого пробела.

Я понимаю логически что нужно сделать, но не понимаю как реализовать:
- Для покупателя это
Покупатель: ООО "Фирма1"
Признак;: ", ИНН: "
ИНН: 1111111111
Признак:", КПП: "
КПП: 222222222
Признак: ", Адрес: "
Адрес: Город, Улица, Дом

Для продукта: Название1 40,000% 0,5000 л., код АП: 15 (Производитель ИНН/КПП: ООО "Производитель1" 2625252525/4535353535)
Название продукта: Название1
Признак пробел перед цифрой "40" после признака:",000% "

Объем: 0,50
Признак "00 л., код АП: "
Код: АП:15
Признак: (Производитель ИНН/КПП: "
Наименование производителя: ООО "Производитель1"
2625252525/4535353535)
Тут признак "/" и первый пробел влево ИНН и СПРАВА от / до ) КПП
2625252525/4535353535)

Я понимаю как сделать это через левсимв, правсимв, номера символа, но это будет грамозко и строк очень много для формульной обработки. Как на PQ сделать не понимаю синтаксис.

Буду благодарен если поможете с реализацией.
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
У меня такой вариант
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    group = Table.Group(Source, {"№ п\п"}, {{"a", each let a=_{0},b=Text.Split(a[Покупатель],":"),
    c=Text.Split(a[#"Наименование продукции"],":"),n=List.Transform(List.FirstN(b,3),
    each Text.BeforeDelimiter(_, ",",{0,1}))&List.LastN(b,1),d={Text.BeforeDelimiter(c{1},
    " (")}&{Text.BeforeDelimiter(c{2}, " ",{0,1})}&Text.Split(Text.BetweenDelimiters(c{2}," ",")",
    {0,1}),"/"),e=Text.BeforeDelimiter(c{0},"., код АП"),f={Text.BeforeDelimiter(e," ",{2,1})}&
    {Text.BetweenDelimiters(e, " ", " ", {1,1}, 0)}&{Text.BetweenDelimiters(e, " ", " ",{2,1},0)},
    l=Record.ToList(a) in {l{0},l{1},l{2}}&n&{l{7},l{8}}&f&d&{l{16},l{17},l{18},l{19},l{20}}}}),
    Custom1 = Table.FromRows(group[a],Table.ColumnNames(Source))
in
    Custom1
 
Не элегантно, но лучше  как-то не придумал
Изменено: buchlotnik - 09.08.2020 00:37:04
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, большое спасибо.
Не понял как работает, но вроде работает. Если есть время и желание могли бы рассказать об использованных структурах?

Попробую в понедельник на полном объеме.
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Михаил Л,
спасибо. Вроде тоже отрабатывает, в понедельник смогу сравить результат двух обработок и поделиться результатами.
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
phelex написал:
рассказать об использованных структурах
о каких структурах речь? в смысле что непонятно?
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, используемые команды объяснить если есть возможность.

Проверил вроде все супер распознало.

Огромное спасибо!
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
phelex написал:
вроде все супер распознало
у тёзки-то решение пошустрее будет, мне же было просто интересно List.Accumulate примотать.

смысл прост - имеем 2 списка: названия будущих столбцов и разделители значений - это a,b для покупателей и  c,d для продукции.
из двух списков формируем один список списков, в котором каждый элемент содержит название столбца и два разделителя - с которого и по который вынимать значения - этот список формирует функция k. За само вынимание значения между двумя разделителями отвечает функция l.
Ну и основная функция - функция n - которой мы скармливаем строковое значение и два списка, через функцию k получаем список для вынимания и последовательно с помощью  List.Accumulate добавляем к пустой записи [] нужные поля через Record.AddField - с заданным названием и значением в виде текста между разделителями (от функции l)
Поскольку логика изначально подразумевает вынимать текст между разделителями, строка для покупателя обрамляется решеткой и собакой "#"&_&"@" - таких символов, по идее, в названии быть не должно и они использованы в качестве ведущего и последнего разделителей; а с продукцией добавлен танец с бубном для поиска первого пробела перед % - функция m - она находит позиции всех пробелов, позицию % и заменяет первый пробел перед % на @,а также добавляет решетку в качестве ведущего разделителя.

вроде всё - писать на М как-то проще, чем по-русски  ;)
Изменено: buchlotnik - 11.08.2020 20:48:16
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik,спасибо за комментарий.
Например такие позиции: Вино высокого качества установленного места производства белое (ОДЭС 11-13%) полусладкое (МКС 18-45 г.дм.куб) "Malesan Bordeaux AOC"/"Малезан Бордо АОС" год урожая 2010,2011,2012 11,000% 0,7500 л., код АП: 0015081000002293377 (Производитель/импортер ИНН/КПП: ООО "КАСТЕЛЬ МАЛЕЗАН" 5020038139/502001001)

Разбивает:
Название: Вино высокого качества установленного места производства белое (ОДЭС
%: 11-13%) полусладкое (МКС 18-45 г.дм.куб) "Malesan Bordeaux AOC"/"Малезан Бордо АОС" год урожая 2010,2011,2012 11,0
Объем и далее корректно.

Т.е. он отсекает название до %, а в названии они могут быть. Если верно понимаю нужно поправить строчку:
   d={"#","@","% "," л., код АП: ","(Производитель/импортер ИНН/КПП: "," ","/",")"},
на такую
   d={"#","@","00% "," л., код АП: ","(Производитель/импортер ИНН/КПП: "," ","/",")"},

Но почему то не меняется результат.
Как он определяет конец названия?

Спасибо
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Переосмыслил, понял что нужно корректировать функцию M.
Нашел финальную особенность, есть товары:
Российское шампанское белое брют "Ариант" 11,750% 0,7500 л., код АП: 0017418000002617303 (Производитель/импортер ИНН/КПП: ООО "ЦПИ-Ариант" 7423012592/744801001)

где формат % не ?,??00%, а почему то ??,???%.
Кто подскажет служебный символ "один любой символ"?
Т.е. в функции m искать не "%", а "???%"
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
phelex написал:
Например такие позиции:
и где эти позиции были в исходном примере?
Так, напомню
Цитата
phelex написал:
Признак: (Производитель ИНН/КПП:
а здесь
Цитата
phelex написал:
(Производитель/импортер ИНН/КПП:
как говорится, почувствуйте разницу
Цитата
Андрей VG написал:
P. S. Ну, и как всегда с задачами подобного плана висит ожидание - ой, я вот такой момент упустил, а вот ещё такой вариант почему-то не обрабатывает
Изменено: buchlotnik - 12.08.2020 14:18:50
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, я понимаю что позиций много и в примере привел общую структуру.
Производитель я подправил. Гуглю какой спец символ может заменить "?" в PQ. Чтобы функцию M скорректировать до  "%", а "???%", пока не нашел за 3 запроса.
Результаты гугла кончились, символ ? - не помог... Вся надежда на участников форума.

Подскажите пожалуйста.

Заранее спасибо.
Изменено: phelex - 12.08.2020 14:44:47
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
phelex написал:
в примере привел общую структуру
на поверку она оказалась не такой уж и общей, пальцем в небо переписывать код мало кому надо.
может поможет :
m=(t)=>[a=Text.PositionOf(t," ",Occurrence.All),b=Text.PositionOf(t,"%",Occurrence.Last),c=List.Last(List.Select(a,each _<b)),d="#"&Text.ReplaceRange(t,c,1,"@")][d],
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik,ееее!
То что нужно! Отработало на УРА!
А все же насчет спецсимволов, они вообще существуют для PQ?
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
phelex написал:
они вообще существуют для PQ
не-а, регулярки есть, но по мне так проще на VBA
Соблюдение правил форума не освобождает от модераторского произвола
 
Изменил
 
Михаил Л,работает, но почему то при рассмотрении результата дух кодов ваш выдает 158К строк, а код buchlotnik, 431К.

Столкнулся еще с 10 исключениями из логики, но их просто на этапе обработки заменю. под необходимый формат.
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
Цитата
phelex написал:
ваш выдает 158К строк, а код  buchlotnik , 431К.
А скармливаете 600К?
 
Михаил Л, да данные берутся из одной таблицы
невозможное делаем сразу, чудо - требует небольшой подготовки.
 
phelex, пробуйте с индексом. Увидите каких строк не хватает. И эти входные данные(строк 20) покажите в файле
И напишите сколько времени уходит на работу запросов при 600К исходных
Изменено: Михаил Л - 12.08.2020 17:18:05
 
Михаил Л, разобрался, причина вновь в кривом запросе от меня.
№ п\п - который вы используете может повторяться, есть данные с нескольких фирм в каждой нумерация идет от 1 до Х. Т.е. 1 может быть до 5 раз. Код прекращает работу как только встречает вторую единицу. Поменял в исходных данных от 1 до Х без повторений:

Но при углубленном тестировании оказалось что даже эти строки почему то не обрабатываются.

Выдает ошибку:
На 4 шаге: Custom1 = Table.FromRows(group[a],Table.ColumnNames(#"Replaced Value1"))
Код
Expression.Error: 21 ключей было указано, но 22 значений предоставлено.
Сведения:
    List
невозможное делаем сразу, чудо - требует небольшой подготовки.
Страницы: 1
Наверх