Есть такая проблема, что Power Query не берет форматы из ячеек листа, только сами значения. Зачастую на это можно наплевать, но часто информация на листе организована в структуру - иерархию строк, которые "плюсиками" сворачиваются/разворачиваются. Бывает, что разобрать эту структуру можно только получив/обозначив уровень такой иерархии где-то на листе, так как других признаков нет. А добыть номер уровня можно только при помощи VBA, или проставить руками.
В общем, говоря короче, наваял функцию для PQ, которая подтягивает к информации на листе еще и уровень иерархии строк, если таковая присутствует. Пользуйтесь.
В редакторе заменяем код на скопированный -> получаем функцию.
Скармливаем функции три параметра:
Полный путь к файлу (включая имя файла) xlsx или xlsm (например, "C:\PQ\test.xlsm")
Имя листа (например, "Лист2"), который хотим получить (необязательно), или список имен листов типа {"Лист1", "Лист5"}. Если ничего не укажем - будет результат по всем листам.
Параметр true/false (необязательно) - хотим ли, чтобы уровень иерархии строк был добавлен первым столбцом к данным, полученным с листа. По умолчанию true.
Радуемся.
Если последний параметр true/null/опущен - получаем к стандартному выводу функции Excel.Workbook дополнительно два столбца: 1. Таблица из двух столбцов: RowIndex (0-based номер строки) и outlineLevel (уровень иерархии) 2. Таблица, содержащая исходные данные из столбца Data, к которой первым столбцом (outlineLevel) прицеплен уровень иерархии.
Если последний параметр false, то получаем только первый столбец из упомянутых выше, и что с ним делать - придумываем сами.
В следующих планах - уровень отступа в ячейках. Но это уже сильно сложнее (не по извлечению, а по притягиванию к таблицам PQ).
Максим, не сочтите за наглость, но может у вас есть простенький файл-пример с этой самой иерархией, чтобы наглядно увидеть об чем речь. А то мне такое встречается редко, а так если столкнусь, хоть буду знать что эту фигню можно победить. Я так смутно представляю, что речь скорее всего о каких-нибудь заумных отчетах из 1С, но все же.
да, как правило это 1С, но не только. И не обязательно заумный, достаточно просто выводить в отчете иерархию или несколько группировок. (п.с. не во всех конфигурациях возможно выведение в плоскую таблицу или соответствующая настройка отчета) В файле числа поудалял, но стояли во всех ячейках.
Максим, спасибо! Вещь очень крутая и полезная. В моей работе очень пригодится. К сожалению, у меня отказывается работать. Посмотрю завтра, на свежую голову. Уже 2 дня не могу справится с этой задачей, отказывается работать.
Максим Зеленский, PooHkrd, Друзья, подскажите пожалуйста, как вы добавили столбец outlineLevel (уровень иерархии)? Какая функция в PQ за это отвечает, куда нужно кликнуть?
Darkhan, простите, не понял вопрос. В первом сообщении темы подробно расписано, откуда берется столбец с уровнем иерархии. Встроенной возможности в PQ для этого нет, кликнуть некуда, поэтому и написана пользовательская функция.
Максим Зеленский, извините за мою невнимательность, теперь понял. Лучше всего это просто скопировать вашу работу и изменять свои таблицы уже:) Спасибо за проделанный труд! Мне лично очень помогает. Ускорил обработку некоторых данных и сэкономил время. По вашему примеру планирую свести время на обработку данных к минимуму, PQ творит чудеса.
Максим Зеленский, а можно еще поинтересоваться, может быть вы как-то развивали идею вашего кода, а именно обрабатывали до базы, в которой сохранялся бы только уникальные значение, которые формируют "Итого" (как в файле лист "Конечный файл"). Глобально можно было бы выполнить такую задачу: 1. Проделываешь один раз операцию для какого-либо файла (например ОСВ из 1С) - перевод из исходного файла в конечный 2. Переподгружаешь другой ОСВ файл для которого выполнялось бы такая же последовательность как и для предыдущего ОСВ 3. Получаешь на выходе новый конечный файл
Просто я не понимаю как из существующего инструментария это возможно было бы относительно быстро сделать и унифицировать работу.
PooHkrd, Я не говорил о том, что у меня есть проблема в переподгрузке файла. Мой вопрос относится напрямую к теме, так как данный код определяет уровень иерархии каждый строчки. Мой вопрос состоит в том, каким образом это дальше можно использовать? И возможно ли как-то преобразовать данные в power query, как продемонстрировано в примере в файле - из "Исходный файл" в "Конечный файл".
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Thorson, конкретно файл "1С новый", который вы прикрепили, имеет одну нехорошую особенность - в нем разноуровневая иерархия, то есть номенклатура может встречаться и на 3, и на 4 уровне, и определить, что на данном уровне является номенклатурой, а что нет - достаточно сложно. Но можно. Если добавить в файл, например, столбец "Код номенклатуры", то тогда можно быстрее разобрать автоматически с сохранением иерархии. Ну или разобрать руками - в любом случае, у нас есть возможность уровни раскидать по столбцам. Если выгружаемый файл всегда имеет одно и то же количество уровней иерархии, то тогда будет работать на повторе. Конкретно этот файл-пример разобрал двумя способами, только при помощи кнопок вот примерно так. При помощи написания кода можно, конечно, гораздо элегантнее и с учетом возможной выгрузки разного количества уровней иерархии, но сейчас лень
А вообще PooHkrd прав, лучше для этого вопроса отдельная тема. Но как пример применения - пусть остается.
Максим Зеленский, огромное спасибо! Подскажите, пожалуйста, как можно обработать с помощью данной функции не один файл, а сразу папку с несколькими файлами?
barina18, делаете запрос к папке и выгружаете содержимое файла без обработки. После этого PQ автоматически создаст запрос с именем вида "Преобразовать пример двоичного файла из имя папки". Вот в этот запрос нужно вставить часть кода запроса, в которым обрабатывается единичный файл из примеров выше. И будет вам счастье.
КБМ, я вам здесь уже указывал, что без конкретных файлов помочь вам трудно. Нужен пример структуры вашего файла, сами данные тут вообще никого не интересуют. Ну и не плохо бы показать файл с запросом, который выдает ошибку.
PooHkrd написал: Нужен пример структуры вашего файла
Ну пожалуйста помогите, с 7 утра вожусь практически, не выходит. Как я уже говорил с одним файлом все отлично, идеально. А вот при запросе к папке я даже не знаю в какой момент нужно и куда вставить этот код. Два файла примера выкладываю.
Практически идеально!!! Спасибо! Нет шага, где можно удалить заголовки (всегда 10 верхних строк в каждом файле), но фильтром по Collumn 3, оставив значения и избавившись от null и другого текста, этот вопрос закрывается!!! Не знаю даже как благодарить Вас и всех, кто участвует в наших проблемах! Огромное спасибо! Кстати, а параметр FullPath теперь не обязательно задавать каждый раз?
КБМ написал: Кстати, а параметр FullPath теперь не обязательно задавать каждый раз?
Н понял вопроса. В параметр вносится путь к файлу. Это чисто для удобства, что не лазить в запрос при необходимости, а менять путь в параметре. Про параметризацию путей к данным есть отличный прием у хозяина сайта.