Как с помощью Power Query преобразовать таблицу с ячейками, разбитыми на несколько, Имеется таблица, где в нескольких столбцах ячейки в строке могут быть разбиты на 2-3. Необходимо значения в этих ячейках распределить в новые созданные столбцы
Добрый день всем. Нужна помощь или хотя бы подсказка где искать. Я е-ком, работаю с маркетплейсами. Поэтому часто приходится делать выгрузки либо по API, либо преобразовывать "сырые данные" прямо из копипасты данных со страницы, если нет возможности выгрузить по API. Сейчас по заданию руководства реализовал выгрузку списка отзывов с ВБ, но на Озоне пока по API они сами не реализовали, поэтому приходится копипастить со страницы. И всё бы ничего, но на Озоне это сделано красиво, но коряво, в том смысле, что - в строке постоянно в разных столбцах ячейки бывают разбиты на несколько. Вот и сейчас то же самое. Хотелось бы автоматизировать подготовку к анализу копируемых данных, но пока не могу понять как. По моей задумке, выглядеть должно так: 1. Разделить ячейки в столбце "Дата и время", создать дополнительный столбец и ячейки (или данные из них) с временем перенести в него на один уровень с датой 2. Разделить ячейки в столбце "Товар", создать дополнительный столбец и ячейки (или данные из них) с артикулом перенести в него на один уровень с датой 3. Тут поинтересней. Разделить ячейки в столбце "Отзыв", создать дополнительные столбцы "Достоинства", "Недостатки" и "Комментарий" и ячейки (или данные из них) соответственно перенести в них на один уровень с датой. Как раскидать - понимаю и почти реализовал. Не понимаю только как распределить или вообще в принципе разделить ячейки. Проблема ещё в том, что часть разделённых данных - по 2, часть - по 3 ячейки. Была мысль для каждого товара довести строки до 3х, а там уже распределять, но, опять же - пока нет опыта как такое реализовать если разбито не одинаково. Файл с листами "исходный" и "результат" для понимания прикладываю. В результативном жёлтым выделил новые создаваемые столбцы. Копируемых строк, само собой, на порядок больше. Буду благодарен любой подсказке
gheser, в последней строке (Table.FromRecords) значение аргумента с названиями колонок можно руками прописать в виде списка. Зря время потратил на их вычисление. Но это так...
AlienSx, код работает в последней версии PQ? У меня в офисе Excel 2013, дома - 2021, и дома не пробовал. В офисе выдаёт ошибку при редактировании в расширенном просмотре. Возможно, из-за несоответствия версий PQ
gheser, да, у меня новая версия (хотя, давно не обновлялся). Обновите свой офисный пакет. Если не поможет, то ищите где произошел сбой. Возможно, что старые версии не поддерживают какие-то параметры в определенных функциях.
AlienSx, один раз прошло на ура, а потом затык, причём, на мой взгляд, на ровном месте. Если принимать один отзыв (может состоять из 2-3 строк в файле) за группу записей - 7 групп записей-отзывов переварил, 8-ю группу почему-то не принимает, хотя она по структуре мало чем оттичается от предыдущей, за исключением кол-ва строк (в 7-й - 3 строки, в 8-й - 2). Но ниже встречается аналогичные структуры, и там проблем нет
Ошибка:
При удалении 2х строк проблемного отзыва запрос отработал корректно. Проблемную группу нашёл при просмотре шагов запроса
Файл с выделенной группой, вызывающей ошибку, приложил
дело не в группе, а в наличии в строке с комментарием. Поправьте разделитель на ": " (с пробелом). Это временно решит проблему - до тех пор, пока не встретится коммент с именно такой подстрокой. Выход - искать самую первую позицию ":" в тексте, чтобы разделить текст по этой позиции. Используйте Text.PositionOf с опцией Occurrence.First и далее Splitter.SplitTextByPositions
AlienSx, спасибо, сначала бы в вообще разобраться с Вашим кодом Что читать / смотреть, чтобы вот так сходу писать запросы на М, без пошагового редактора?
Garrys, спасибо, дома потестю. Корректно будет поменять File.Contents на Folder.Files? Просто чтобы кидать файл в папку и дальше запросом изменить структуру данных как у Вас в коде. Чтобы не было жёсткой привязки к имени файла. Я пока не настолько хорош в кодинге на М напрямую, просто смотрел как выглядит код в расширенном редакторе после применения шагов в PQ и сам правил до нужного результата, а вот так чтобы сразу кодить - ещё не дорос, поэтому могу ошибаться
Garrys, задачу решает? Решает. Почему бы и нет? Повеселила "игра в наперстки" с названиями колонок и в функции Я б так не смог, у меня так голова не работает. Сразу грустно становится, если такой вариант маячит на горизонте Только запятую в конце последней строки (a4) уберите.
gheser, нет, это не совсем так делается. Folder.Files("путь_к_папке") даст вам список файлов. В папке могут быть другие файлы, включая временные. Поэтому, расчет на то, что ваш файл будет стоять в первой строке, может оказаться ошибочным. Но если он вдруг гарантировано оказался на 1м месте, то можно, как вариант, fr = Excel.Workbook(Table.FirstValue(Folder.Files("путь_к_папке")))
все правильно, так и надо. Меня же зачем-то "понесло" через List.PositionOf таблицы нарезать. Но главное здесь - как эту таблицу обработать. Собственно, наши вариации - именно про это.
AlienSx, в папке гарантированно будет только один файл. Я себе так автоматизировал создание отчётов по ежедневным продажам: тупо скачиваю с маркетплейса отчёт за нужный день, перекидываю его в определённую папку с заменой файла, и в файле с отчётом жму "обновить всё". Остальное делает запрос. Обрабатываемый файл в экселе не открываю, поэтому там временного файла не будет. Нужен ли будет при этом Table.FirstValue из Вашего последнего примера кода? AlienSx, Garrys, спасибо за помощь и подсказки!
gheser написал: Нужен ли будет при этом Table.FirstValue из Вашего последнего примера кода?
Во-первых, чтобы не было разночтений - это переделка первой строки кода Garrys. Во-вторых, да - нужен, конечно. Вы не бойтесь - сами пробуйте что-то в расширенном редакторе.
AlienSx, Garrys, вопрос не по теме. Сам вопрос поднимался ранее в отдельной теме, внятного решения тогда не было, но с момента последнего сообщения в ней прошло 3 года. Возможно, решение уже появилось, а у вас опыта всяко больше моего - может, сможете подсказать решение.
Есть ли возможность выгрузки результата (-ов) запроса (-ов) в обычную таблицу, не умную? Без VBA, а прямо из PQ