Страницы: 1 2 След.
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).
Изменено: Максим Зеленский - 19.01.2019 02:02:29 (update)
F1 творит чудеса
 
Максим, не сочтите за наглость, но может у вас есть простенький файл-пример с этой самой иерархией, чтобы наглядно увидеть об чем речь. А то мне такое встречается редко, а так если столкнусь, хоть буду знать что эту фигню можно победить. Я так смутно представляю, что речь скорее всего о каких-нибудь заумных отчетах из 1С, но все же.
Изменено: PooHkrd - 14.08.2017 17:35:42
Вот горшок пустой, он предмет простой...
 
да, как правило это 1С, но не только.
И не обязательно заумный, достаточно просто выводить в отчете иерархию или несколько группировок.
(п.с. не во всех конфигурациях возможно выведение в плоскую таблицу или соответствующая настройка отчета)
В файле числа поудалял, но стояли во всех ячейках.
F1 творит чудеса
 
Доброе время суток.
Максим, спасибо! Серьёзная работа, будет что поизучать на досуге.
 
Максим, спасибо! Вещь очень крутая и полезная. В моей работе очень пригодится. К сожалению, у меня отказывается работать. Посмотрю завтра, на свежую голову. Уже 2 дня не могу справится с этой задачей, отказывается работать.
 
Все у вас работает. Просто вы по незнанию допускаете простые ошибки.
Вот горшок пустой, он предмет простой...
 
PooHkrd да, вы были правы. Все работает :)
Спасибо огромное за помощь!
 
Максим Зеленский, PooHkrd, Друзья, подскажите пожалуйста, как вы добавили столбец outlineLevel (уровень иерархии)? Какая функция в PQ за это отвечает, куда нужно кликнуть? :)
Изменено: Darkhan - 05.09.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 творит чудеса
 
Максим Зеленский, огромное спасибо! Подскажите, пожалуйста, как можно обработать с помощью данной функции не один файл, а сразу папку с несколькими файлами?
 
Максим Зеленский,
Не думал что на PQ возможны такие вещи. Огромное спасибо!!

P.S. Восхищаюсь вашими знаниями и уровнем владения М (и не только М).  
 
barina18, делаете запрос к папке и выгружаете содержимое файла без обработки. После этого PQ автоматически создаст запрос с именем вида "Преобразовать пример двоичного файла из имя папки". Вот в этот запрос нужно вставить часть кода запроса, в которым обрабатывается единичный файл из примеров выше. И будет вам счастье.
Вот горшок пустой, он предмет простой...
 
Максим Зеленский, здравствуйте! Огромное спасибо за мега-инструмент! Жаль, что этот фокус не провернуть с XLS.
 
Сергей, PQ вообще не очень любит XLS и XLSB. Лучше избегать этих форматов любым способом
F1 творит чудеса
 
Цитата
PooHkrd написал:
нужно вставить часть кода
Подскажите пожалуйста какую часть кода нужно вставлять? Выдает ошибку, но с одним файлом все вроде работает хорошо.
 
КБМ, я вам здесь уже указывал, что без конкретных файлов помочь вам трудно. Нужен пример структуры вашего файла, сами данные тут вообще никого не интересуют. Ну и не плохо бы показать файл с запросом, который выдает ошибку.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Нужен пример структуры вашего файла
Ну пожалуйста помогите, с 7 утра вожусь практически, не выходит. Как я уже говорил с одним файлом все отлично, идеально. А вот при запросе к папке я даже не знаю в какой момент нужно и куда вставить этот код. Два файла примера выкладываю.
 
вот так хотели
 
КБМ, вот так можно.
Вот горшок пустой, он предмет простой...
 
Цитата
azma написал:
вот так хотели
Так у меня получалось, да. С одним файлом проблем не было.
 
Цитата
PooHkrd написал:
вот так можно
Практически идеально!!! Спасибо! Нет шага, где можно удалить заголовки (всегда 10 верхних строк в каждом файле), но фильтром по Collumn 3, оставив значения и избавившись от null и другого текста, этот вопрос закрывается!!! Не знаю даже как благодарить Вас и всех, кто участвует в наших проблемах! Огромное спасибо!
Кстати, а параметр FullPath теперь не обязательно задавать каждый раз?
Изменено: КБМ - 06.09.2019 08:21:57
 
Цитата
КБМ написал:
Нет шага, где можно удалить заголовки
Об этом разговора не было, ну раз надо, то вот:
Скрытый текст

Цитата
КБМ написал:
Кстати, а параметр FullPath теперь не обязательно задавать каждый раз?
Н понял вопроса. В параметр вносится путь к файлу. Это чисто для удобства, что не лазить в запрос при необходимости, а менять путь в параметре. Про параметризацию путей к данным есть отличный прием у хозяина сайта.
Вот горшок пустой, он предмет простой...
Страницы: 1 2 След.
Наверх