Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
[Power Query] Добываем иерархию (вложенность) строк из файла Excel, есть такая функция
 
Делюсь.

Есть такая проблема, что Power Query не берет форматы из ячеек листа, только сами значения.
Зачастую на это можно наплевать, но часто информация на листе организована в структуру - иерархию строк, которые "плюсиками" сворачиваются/разворачиваются.
Бывает, что разобрать эту структуру можно только получив/обозначив уровень такой иерархии где-то на листе, так как других признаков нет. А добыть номер уровня можно только при помощи VBA, или проставить руками.

В общем, говоря короче, наваял функцию для PQ, которая подтягивает к информации на листе еще и уровень иерархии строк, если таковая присутствует.
Пользуйтесь.

https://github.com/hohlick/Power-Query-Excel-Formats/blob/master/RowsOutline/ExcelWorksheetsRowOutlines.pq

Описание на буржуинском: https://github.com/hohlick/Power-Query-Excel-Formats/tree/master/RowsOutline

Как пользоваться:

  1. Копируем код
  2. Создаем пустой запрос в PQ
  3. В редакторе заменяем код на скопированный -> получаем функцию.
Скармливаем функции три параметра:
  1. Полный путь к файлу (включая имя файла) xlsx или xlsm (например, "C:\PQ\test.xlsm")
  2. Имя листа (например, "Лист2"), который хотим получить (необязательно), или список имен листов типа {"Лист1", "Лист5"}. Если ничего не укажем - будет результат по всем листам.
  3. Параметр true/false (необязательно) - хотим ли, чтобы уровень иерархии строк был добавлен первым столбцом к данным, полученным с листа. По умолчанию true.

Радуемся.

Если последний параметр true/null/опущен - получаем к стандартному выводу функции Excel.Workbook дополнительно два столбца:
1. Таблица из двух столбцов: RowIndex (0-based номер строки) и outlineLevel (уровень иерархии)
2. Таблица, содержащая исходные данные из столбца Data, к которой первым столбцом (outlineLevel) прицеплен уровень иерархии.

Если последний параметр false, то получаем только первый столбец из упомянутых выше, и что с ним делать - придумываем сами.

В следующих планах - уровень отступа в ячейках. Но это уже сильно сложнее (не по извлечению, а по притягиванию к таблицам PQ).
Изменено: Максим Зеленский - 17 Авг 2017 20:17:58 (update)
F1 творит чудеса
 
Максим, не сочтите за наглость, но может у вас есть простенький файл-пример с этой самой иерархией, чтобы наглядно увидеть об чем речь. А то мне такое встречается редко, а так если столкнусь, хоть буду знать что эту фигню можно победить. Я так смутно представляю, что речь скорее всего о каких-нибудь заумных отчетах из 1С, но все же.
Изменено: PooHkrd - 14 Авг 2017 17:35:42
 
да, как правило это 1С, но не только.
И не обязательно заумный, достаточно просто выводить в отчете иерархию или несколько группировок.
(п.с. не во всех конфигурациях возможно выведение в плоскую таблицу или соответствующая настройка отчета)
В файле числа поудалял, но стояли во всех ячейках.
F1 творит чудеса
 
Доброе время суток.
Максим, спасибо! Серьёзная работа, будет что поизучать на досуге.
 
Максим, спасибо! Вещь очень крутая и полезная. В моей работе очень пригодится. К сожалению, у меня отказывается работать. Посмотрю завтра, на свежую голову. Уже 2 дня не могу справится с этой задачей, отказывается работать.
 
Все у вас работает. Просто вы по незнанию допускаете простые ошибки.
 
PooHkrd да, вы были правы. Все работает :)
Спасибо огромное за помощь!
 
Максим Зеленский, PooHkrd, Друзья, подскажите пожалуйста, как вы добавили столбец outlineLevel (уровень иерархии)? Какая функция в PQ за это отвечает, куда нужно кликнуть? :)
Изменено: Darkhan - 5 Сен 2018 10:25:10
 
Darkhan, простите, не понял вопрос. В первом сообщении темы подробно расписано, откуда берется столбец с уровнем иерархии.
Встроенной возможности в PQ для этого нет, кликнуть некуда, поэтому и написана пользовательская функция.
F1 творит чудеса
 
Максим Зеленский, извините за мою невнимательность, теперь понял. Лучше всего это просто скопировать вашу работу и изменять свои таблицы уже:)  Спасибо за проделанный труд! Мне лично очень помогает. Ускорил обработку некоторых данных и сэкономил время. По вашему примеру планирую свести время на обработку данных к минимуму, PQ творит чудеса.
 
Цитата
Darkhan написал:
Лучше всего это просто скопировать вашу работу и изменять свои таблицы уже
Только так это и работает пока, увы :)
F1 творит чудеса
 
Максим Зеленский,
а можно еще поинтересоваться, может быть вы как-то развивали идею вашего кода, а именно обрабатывали до базы, в которой сохранялся бы только уникальные значение, которые формируют "Итого" (как в файле лист "Конечный файл"). Глобально можно было бы выполнить такую задачу:
1. Проделываешь один раз операцию для какого-либо файла (например ОСВ из 1С) - перевод из исходного файла в конечный
2. Переподгружаешь другой ОСВ файл для которого выполнялось бы такая же последовательность как и для предыдущего ОСВ
3. Получаешь на выходе новый конечный файл

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

Заранее спасибо!
 
Доброго дня.
Цитата
Thorson написал:
2. Переподгружаешь другой ОСВ файл для которого выполнялось бы такая же последовательность как и для предыдущего ОСВ
В чем у вас проблема изменить источник к файлу в запросе? Это делается двумя нажатиями кнопки мыши.
И как вообще ваш вопрос относится к теме?
 
PooHkrd,
Я не говорил о том, что у меня есть проблема в переподгрузке файла.
Мой вопрос относится напрямую к теме, так как данный код определяет уровень иерархии каждый строчки. Мой вопрос состоит в том, каким образом это дальше можно использовать? И возможно ли как-то преобразовать данные в power query, как продемонстрировано в примере в файле - из "Исходный файл" в "Конечный файл".  
 
Привести данные в отчете к виду плоской таблицы, чтобы далее можно было строить на её основе аналитическую модель. Вот пример, чего юзеры хотели, и как я эту функцию от Максима применил.
Чой-та движок форума тупит, не работает кнопка вставки ссылки, потому публикую в таком виде, господа модераторы, уж не обессудьте.
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=65278&TITLE_SEO=65278-privesti-dannye-iz-1s-v-nuzhnyy-vid-dlya-svodnoy-v-eksel&MID=891027#message891027
 
Максим Зеленский, спасибо!  :idea:
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
Thorson, конкретно файл "1С новый", который вы прикрепили, имеет одну нехорошую особенность - в нем разноуровневая иерархия, то есть номенклатура может встречаться и на 3, и на 4 уровне, и определить, что на данном уровне является номенклатурой, а что нет - достаточно сложно. Но можно.
Если добавить в файл, например, столбец "Код номенклатуры", то тогда можно быстрее разобрать автоматически с сохранением иерархии.
Ну или разобрать руками - в любом случае, у нас есть возможность уровни раскидать по столбцам.
Если выгружаемый файл всегда имеет одно и то же количество уровней иерархии, то тогда будет работать на повторе.
Конкретно этот файл-пример разобрал двумя способами, только при помощи кнопок  вот примерно так. При помощи написания кода можно, конечно, гораздо элегантнее и с учетом возможной выгрузки разного количества уровней иерархии, но сейчас лень

А вообще PooHkrd прав, лучше для этого вопроса отдельная тема. Но как пример применения - пусть остается.
F1 творит чудеса
Страницы: 1
Читают тему (гостей: 1)
Наверх