Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Sergey Stoyanov
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
power query развернуть каталог вида: category, id, parentId в отдельные столбцы с подкатегориями, xml файл со списком родительских и вложенных категорий в одном столбце связанных по id/parentid разнести по столбцам по вложенности
 
#
21.06.2024 16:33:13
Доброго дня,
Возникла задача пересобрать получаемые из интернета данные в виде XML в читаемую таблицу.  В xml 2 таблицы - в одной дерево каталога в виде названия,id, и родительского id, во второй товары  c id категории.  В итоге хочется получить таблицу с товарами и столбцами всех подкатегорий. Т.е нужно развернуть список категорий c id/parent id в отдельные столбцы и добавить к товару.  Возникла мысль о Power Query. Но, так как я с PQ практически не сталкиваюсь и особо его не знаю, решать пришлось в лоб, через объединение. А потом еще сдвигать короткие цепочки. Да и число подкатегорий экспериментально только получил.  Решение как бы есть, но мне не особо нравиться.  Может что-нибудь более изящное кто предложит? А то самому знаний/опыта не хватает. Заодно и поучиться. Подобных задач что-то не нашел, может формулирую не правильно.
Мое решение ниже и в приложенном файле. Пример xml обрезал по товарам до приемлемого и сохранил в текстовом, В оригинале в источнике ссылка api
Код
let
    Источник = Xml.Tables(File.Contents("C:\...\sampleFeed.txt")){0}[shop],
    category = Источник{0}[categories]{0}[category],
    headrename = Table.RenameColumns(category,{{"Element:Text", "Tree"}, {"Attribute:id", "id"}, {"Attribute:parentId", "parentId"}}),
    listforfilter = List.Buffer(headrename[parentId]),
    tree_last = Table.SelectRows(headrename, each ([id] <> listforfilter)),
    joinL1 = Table.NestedJoin(tree_last,{"parentId"},headrename,{"id"},"t1",JoinKind.LeftOuter),
    #"treelast-1" = Table.ExpandTableColumn(joinL1, "t1", {"Tree", "parentId"}, {"t1.Tree", "t1.parentId"}),
    joinL2 = Table.NestedJoin(#"treelast-1",{"t1.parentId"},headrename,{"id"},"t1",JoinKind.LeftOuter),
    #"treelast-2" = Table.ExpandTableColumn(joinL2, "t1", {"Tree", "parentId"}, {"t1.Tree.1", "t1.parentId.1"}),
    joinL3 = Table.NestedJoin(#"treelast-2",{"t1.parentId.1"},headrename,{"id"},"t1",JoinKind.LeftOuter),
    #"treelast-3" = Table.ExpandTableColumn(joinL3, "t1", {"Tree"}, {"t1.Tree.2"}),
    recs_temp = Table.ToRecords(Table.ReorderColumns(#"treelast-3",{"t1.Tree.2", "t1.Tree.1", "t1.Tree", "Tree", "id", "parentId", "t1.parentId", "t1.parentId.1"})),
    recs_tree = List.Transform(recs_temp, each 
        if _[t1.Tree]=null then _&[t1.Tree.2=_[Tree], Tree = null] 
        else 
            if _[t1.Tree.1]=null then _&[t1.Tree.2=_[t1.Tree], t1.Tree.1=_[Tree], t1.Tree=null, Tree = null] 
            else 
                if _[t1.Tree.2]=null then _&[t1.Tree.2=_[t1.Tree.1], t1.Tree.1=_[t1.Tree], t1.Tree=_[Tree], Tree = null] 
                else _),
    table_tree = Table.FromRecords(recs_tree),
    offer = Источник{0}[offers]{0}[offer],
    join_offer_tree = Table.NestedJoin(offer,{"categoryId"},table_tree,{"id"},"temp",JoinKind.LeftOuter),
    result = Table.ExpandTableColumn(join_offer_tree, "temp", {"t1.Tree.2", "t1.Tree.1", "t1.Tree", "Tree"}, {"temp.t1.Tree.2", "temp.t1.Tree.1", "temp.t1.Tree", "temp.Tree"})
in
    result
Изменено: Sergey Stoyanov - 21.06.2024 16:34:00
 
Перейти    
PQ применить подстановочную таблицу только к уникальным значениям, а потом заполнить, Из списка выбрать уникальные - применить подстановку в PQ и затем заполнить все значения
 
#
16.02.2023 18:24:27
Есть подстановочная таблица порядка 2000 строк. Формулами в таблице 100к  это все достаточно долго обсчитывается. Решил PQ начать осваивать уже. В принципе, здесь видел решение и я его себе адаптировал. Уже приемлемо, но тоже не быстро. Потом решил, что можно создать вторую таблицу с уникальными значениями нужного столбца, сделать подстановку и объединить таблицы. Вопрос, а нужно ли второе подключение? Но, понять как фильтровать в одном запросе уникальные, а потом заполнить по типу впр($a1;$a$1:$b1;2;0) у меня пока не получается. Так что, с тремя запросами делаю:
1.Запрос к данным, только подключение:
Код
 let
    Источник = Folder.Files("C:\Users\Documents\Продажи"),
    Conv = Table.SelectColumns(Table.AddColumn(Источник, "Преобразовать файл из Продажи", each #"Преобразовать файл из Продажи"([Content])), {"Name", "Преобразовать файл из Продажи"}),
    Tabl = Table.Skip( Table.ExpandTableColumn(Conv, "Преобразовать файл из Продажи2022", Table.ColumnNames(#"Преобразовать файл из Продажи"(#"Пример файла"))),3),
    #"Повышенные заголовки" = Table.PromoteHeaders(Tabl, [PromoteAllScalars=true]),
    #"Фильтр пустых" = Table.SelectRows(#"Повышенные заголовки", each ([Товар] <> "" and [Товар] <> " ") and ([#"Кол-во"] <> "0")),
    Путь2 = Table.AddColumn(#"Фильтр пустых", "Путь2", each try Text.Start([ПУТЬ],Text.PositionOf([ПУТЬ],"/", Occurrence.All){1}) otherwise [ПУТЬ] )
in
    Путь2
2. Запрос к уникальным и подстановка, только подключение
Код
let
    Источник = Table.Distinct(Продажи[[Товар]]),
    LTrim = Table.AddColumn(Источник, "LeftTrim", each Text.TrimStart([Товар],{"+","/", " "})),
     categories_rec = [ src  = Excel.Workbook(File.Contents("C:\Users\ТабПодстановки.xlsx"), null, true){[Item="Вендор",Kind="Table"]}[Data],
    sort = Table.Sort(src,{{( s ) =>  Text.Length ( s[Сравнение] ), Order.Ascending}}),
    recs = List.Buffer ( Table.ToRecords ( sort ) ) ][recs],
    Подстановка =  Table.AddColumn (LTrim, "Вендор0", each if Text.StartsWith([LeftTrim], "Intel") then "Intel" else if Text.StartsWith([LeftTrim], "AMD") then "AMD" else List.Last ( List.Select ( categories_rec, ( x ) => Text.Contains ( [Товар], x[Сравнение], Comparer.OrdinalIgnoreCase ) ) )[Вендоры]? ),
    #"Удаленные столбцы" = Table.RemoveColumns(Подстановка,{"LeftTrim"})
in
    #"Удаленные столбцы"

3. Слияние и выгрузка:

Код
let    
    Источник = Table.NestedJoin(Продажи,{"Товар"},Подстановка,{"Товар"},"Запрос1",JoinKind.LeftOuter),    
    #"Развернутый элемент Запрос1" = Table.ExpandTableColumn(Источник, "Запрос1", {"Вендор0"}, {"Запрос1.Вендор0"})
in    
    #"Развернутый элемент Запрос1"

У меня вроде все работает и так, просто с точки зрения оптимизации хотел совета спросить. Ну и разбираться с PQ получше.

Изменено: Sergey Stoyanov - 16.02.2023 19:34:00
 
Перейти    
Страницы: 1
Loading...