Страницы: Пред. 1 2
RSS
[Power Query] Добываем иерархию (вложенность) строк из файла Excel, есть такая функция
 
Максим добрый день.
Огромное вам спасибо за вашу функцию, очень выручает.
 
Mirdv, пожалуйста :)
F1 творит чудеса
 
Максим, а вы не знаете еще каких-либо товарищей-любителей покопаться в xml/binary структуре файлов-источников. А то блог гражданина Ken R накрылся медным тазом, и это горе великое есть (хорошо хоть успел урвать самую мякотку), там пропало столько полезнейшего кода. Может знаете на него ссылку на github?
Вот горшок пустой, он предмет простой...
 
PooHkrd, жаль, не знал, хороший был блог.
Насколько знаю, у Имке было несколько постов (В основном про забирание заданного интервала ячеек). Больше не встречал, не скажу
F1 творит чудеса
 
PooHkrd, к счастью, есть WebArchive
web.archive.org/web/20190731141708/http://www.excelandpowerbi.com/
Изменено: Андрей Лящук - 04.02.2020 05:45:02
 
Цитата
Андрей Лящук написал:
к счастью
На сайте  заинтересовал запрос. Все хорошо, но значения рассыпаются по строкам. Нет ли возможности достать и номер строки, с которой это значение в исходнике?
Добавил в конце шаг с номерами строк, проставленных вручную.
Код
let
    Source = File.Contents("D:\Новая папка\word\Original\Документы\24.docx"),
 
  


   DecompressFiles=(ZIPFile, Position, FileToExtract, XMLSoFar) => 
    let 
       MyBinaryFormat = try BinaryFormat.Record([DataToSkip=BinaryFormat.Binary(Position), 
                 MiscHeader=BinaryFormat.Binary(18), 
                 FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
                 UnCompressedFileSize=BinaryFormat.Binary(4),
                 FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
                 ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
                 TheRest=BinaryFormat.Binary()]) otherwise null,
 
    MyCompressedFileSize = try MyBinaryFormat(ZIPFile)[FileSize]+1 otherwise null,
    MyFileNameLen = try MyBinaryFormat(ZIPFile)[FileNameLen] otherwise null,
    MyExtrasLen = try MyBinaryFormat(ZIPFile)[ExtrasLen] otherwise null,
 
    MyBinaryFormat2 = try BinaryFormat.Record([DataToSkip=BinaryFormat.Binary(Position), Header=BinaryFormat.Binary(30), Filename=BinaryFormat.Text(MyFileNameLen), Extras=BinaryFormat.Binary(MyExtrasLen), Data=BinaryFormat.Binary(MyCompressedFileSize), TheRest=BinaryFormat.Binary()]) otherwise null,
 
    MyFileName = try MyBinaryFormat2(ZIPFile)[Filename] otherwise null,
    GetDataToDecompress = try MyBinaryFormat2(ZIPFile)[Data] otherwise null,
    DecompressData = try Binary.Decompress(GetDataToDecompress, Compression.Deflate) otherwise null,
    NewPosition = try Position + 30 + MyFileNameLen + MyExtrasLen + MyCompressedFileSize - 1 otherwise null,
    #"Imported XML" = DecompressData,
    //#"Imported XML" = DecompressData ,
    #"Added Custom" = try Table.AddColumn(#"Imported XML", "Filename", each MyFileName) otherwise #"Imported XML",
 
    #"Appended Query" = if #"Imported XML" = null then XMLSoFar else if (MyFileName = FileToExtract) then #"Added Custom" else
         if (FileToExtract = "") and Position <> 0 then Table.Combine({#"Added Custom", XMLSoFar}) 
         else #"Added Custom"
    
    in
     if  (MyFileName = FileToExtract) or (#"Appended Query" = XMLSoFar) then
       #"Appended Query"
     else 
       @DecompressFiles(ZIPFile, NewPosition, FileToExtract, #"Appended Query"),










    ReplaceTag = (CurrentTag, NewTag, MyText) =>
    let
   // Replace all CurrentTag with NewTag,
   Pos = Text.PositionOf(MyText, CurrentTag),
   EndPos = Text.PositionOf(Text.Range(MyText, Pos),">"),
   NewText = try Text.ReplaceRange(MyText, Pos, EndPos+1, NewTag) otherwise MyText
    in
    if  (Pos = -1) then
       NewText
     else 
       @ReplaceTag(CurrentTag, NewTag, NewText),
 
    // Extract the document.xml from the Word docx file using DecompressZIP approach from previous blog post
 
    MyXML = DecompressFiles(Source, 0, "word/document.xml", ""),
 
    // Convert the output into lines of text
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(MyXML,null,null,65001)}),
 
    //Some Row Start tags have attributes and need to be handled using ReplaceTag function
    #"Added Custom" = Table.AddColumn(#"Imported Text", "Custom", each ReplaceTag("<w:tr ", "||ROW START||", [Column1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Column1"}}),
 
    //Borrowed code that removes all XML tags
    //https://social.technet.microsoft.com/Forums/en-US/7ec64d6d-c3fc-4110-94c7-2e0087171475/how-to-remove-html-tags-from-a-column?forum=powerquery
 
    ReplaceStr = Table.ReplaceValue(#"Renamed Columns","><",">,<",Replacer.ReplaceText,{"Column1"}),
    ListsCol = Table.AddColumn(ReplaceStr, "ID", each 
               Text.Split(Text.Combine(List.Alternate(Text.SplitAny([Column1],"<>"),1,1,1),","),",")),
    ExpCol = Table.ExpandListColumn(ListsCol, "ID"),
    FilterID = Table.SelectRows(ExpCol, each ([ID] <> "")),
    TheEnd = Table.SelectColumns(FilterID,{"ID"}),
    Custom1 = Table.FromColumns({ TheEnd[ID], {1,1,1,2,2,2,2,2,3,4,5,6,7,8,9,9,9,9,10,11,12,13,14,14,14,14,14,15,15,15 } }, {"ID", "Номер строки в Word"})
in
    Custom1
 
Так у него на сайте был разбор как вытащить из docx именно таблицы. И функция его парсит именно их. А так, ныряйте в текстовое содержимое и смотрите как там в структуре разделяются именно строки в документе, тогда вполне можно будет разобрать и текст по строкам.
З.Ы. Подсказка, если я все верно понял то каждая новая строка начинается с тега <w:p w:rsidR ...
Андрей Лящук, а я и забыл, спасибо.
Изменено: PooHkrd - 04.02.2020 10:12:42
Вот горшок пустой, он предмет простой...
 
Добрый день!

А тут как то можно применить иерархия к первому столбцу?
 
У вас там нет иерархии. У вас там можно по количеству пробелов в начале строки понять где какой уровень, но это как бы уже не в рамках этой темы, создавайте новую - помогу.
Изменено: PooHkrd - 19.02.2020 08:49:33
Вот горшок пустой, он предмет простой...
 
Добрый день! Функция Максима замечательный инструмент для работы с выгрузками из 1с. Сейчас столкнулся с такой проблемой, что данная функция показывает иерархию только до 7 уровня. У меня в файле 12. Подскажите, пожалуйста, какие изменения нужно внести в функцию, что бы она работала с моей иерархией. Спасибо!
Изменено: Andrey_S - 24.04.2020 16:22:14
 
Цитата
Andrey_S написал:
У меня в файле 12
Где 12? Вижу 8, учитывая что функция для первого уровня группировки ничего не проставляет, то все нормально она видит.

Если вы про скрытые группировкой столбцы, то функция не для этого предначена, она видит только группировку строк.
Изменено: PooHkrd - 24.04.2020 17:05:06
Вот горшок пустой, он предмет простой...
 
Добрый день, тут выкладывал дополненный возможностью извлечения indentlevel вариант этой функции
 
Андрей Лящук, Спасибо.
PooHkrd, Если я не ошибаюсь, функция определяет количество отступов в ячейке. В восьмой групперовке несколько иерархий. Согласен, в предоставленном мной примере не 12 иерархий, их 11. ( 0-10)
 
Andrey_S, функция не определяет количество отступов в ячейке. Функция определяет именно уровень в иерархии выковыривая из xml-структуры файла. И если вы видете циферки от 1 до 8, то именно их она и найдёт. Функция Андрея более продвинутая, надеюсь вам она поможет больше.
Вот горшок пустой, он предмет простой...
 
День добрый!
Цитата
PooHkrd написал:
Вот в этот запрос нужно вставить часть кода запроса, в которым обрабатывается единичный файл из примеров выше
Подскажите, пожалуйста, какую именно часть кода необходимо вставить в запрос "Преобразовать пример файла" для групповой обработки файлов в папке.
Заранее большое спасибо!
 
Katerina_23, будет проще если вы покажете ваши файлы, которые нужно обработать и результат, который требуется получить.
Вот горшок пустой, он предмет простой...
 
Доброго времени суток!

А у меня своеобразная задачка. Все получилось по скрипту, но задача соотнести подотделы к отделам, при этом цифра отдела - это сумма всех подотделов. Вложил файл выгрузки с иерархией.
 
Цитата
Shahoff написал:
Все получилось по скрипту, но задача соотнести подотделы к отделам
Если функция из названия темы работает, то вашу задачу следует вынести в отдельную тему. Там вы можете сослаться на эту, как способ получения данных из файла и описать что не получается сделать далее.
В данной теме ваш вопрос - оффтоп.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Если функция из названия темы работает, то вашу задачу следует вынести в отдельную тему. Там вы можете сослаться на эту, как способ получения данных из файла и описать что не получается сделать далее.В данной теме ваш вопрос - оффтоп.

Хорошо. Сделаю. Спасибо.
Изменено: Shahoff - 06.05.2020 14:20:40
 
PooHkrd, вопрос состоит в том, как при помощи функции М. Зеленского обрабатывать в части извлечения иерархии не один файл, а папку с несколькими файлами.
Т.е. чтобы данные в Power Bi обновлялись автоматически при добавлении в папку нового файла. Такое ведь возможно?  
 
Вообще возможно.
В параметрах укажите путь к папке, но не указывайте имя конкретного файла, тогда будут обработаны все. Если укажете в параметрах имя конкретного файла, то будет обработан только он один.
Вот горшок пустой, он предмет простой...
 
PooHkrd, огромное спасибо!  :)  
 
Цитата
Максим Зеленский написал:
А вообще  PooHkrd  прав, лучше для этого вопроса отдельная тема
День добрый. А данная тема (несколько уровней иерархии) где то уже открыта?
 
mitox, в файлах того же сообщения #17 смотрели? Есть там ответ на ваш вопрос?
F1 творит чудеса
 
Максим Зеленский, Только что увидел ответ (не подписался на ответы по теме((((( )
Да. Я посмотрел решение с pivot, но у меня до 8-9 уровней группировки, так что не подходит.
Я в принципе нашел решение на базе вашей функции, но оно громоздким получилось:
- Первым шагом все "Обороты" вынес в дополнительный уровень
- Далее по каждому уровню делаю следующее (с 17 по 20 строки).

Далее обсуждение попадет в офтоп поэтому открыл новую тему и приложил файл тут
Изменено: mitox - 06.07.2020 10:56:08
 
Максим Зеленский, офигеть какая крутая штука, большое спасибо!!!
 
супер все работает. Это просто гениально (отработало на отлично на выгрузке из 1С8)
Автору огромное спасибо!
Страницы: Пред. 1 2
Наверх