Развернуть вложенную таблицу-xml в один ряд PowerQuery

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Развернуть вложенную таблицу-xml в один ряд PowerQuery (Формулы/Formulas)

yuraok

Дата: Понедельник, 25.05.2020, 21:23 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 9

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Есть файл xml. Открываю его с помощью PowerQuery.
Захожу -> offer (table) - нужен столбец param (со вложенными таблицами

Есть некая таблица с параметрами товаров и в столбце param есть также вложенные параметры отдельно взятой позиции в табличке.

--рисунок--
https://u.to/Ts_HGA

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

--рисунок--
https://u.to/Tc_HGA

Мне нужно чтобы ети параметры во вложенной таблице выводились не вниз

--рисунок--
https://u.to/TM_HGA

, а в одну строку попорядке по в столбцах.

--рисунок--
https://u.to/T8_HGA

ТОБИШЬ чтобы вложенные таблицы развернуть по ширине в длинну, тогда в исходной таблице названия не будут дублироватся, а все будет идти в один ряд.

Файл примера прикрепил. https://drive.google.com/open?id=1DcQ1YrFdDU4h4S8K_6CJKDiZgHgo2zeL - тут файл примера

Понимаю что задача непростая. (по крайней мере для меня очень сложная)
Как ее можно решить? Я в екселе чайник.
Буду ОООЧЕНЬ БЛАГОДАРЕН ЗА ПОМОЩЬ.

К сообщению приложен файл: primer1.xml (49.9 Kb)

Сообщение отредактировал yuraok - Понедельник, 25.05.2020, 21:28

Ответить

СообщениеЕсть файл xml. Открываю его с помощью PowerQuery.
Захожу -> offer (table) - нужен столбец param (со вложенными таблицами

Есть некая таблица с параметрами товаров и в столбце param есть также вложенные параметры отдельно взятой позиции в табличке.

--рисунок--
https://u.to/Ts_HGA

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

--рисунок--
https://u.to/Tc_HGA

Мне нужно чтобы ети параметры во вложенной таблице выводились не вниз

--рисунок--
https://u.to/TM_HGA

, а в одну строку попорядке по в столбцах.

--рисунок--
https://u.to/T8_HGA

ТОБИШЬ чтобы вложенные таблицы развернуть по ширине в длинну, тогда в исходной таблице названия не будут дублироватся, а все будет идти в один ряд.

Файл примера прикрепил. https://drive.google.com/open?id=1DcQ1YrFdDU4h4S8K_6CJKDiZgHgo2zeL - тут файл примера

Понимаю что задача непростая. (по крайней мере для меня очень сложная)
Как ее можно решить? Я в екселе чайник.
Буду ОООЧЕНЬ БЛАГОДАРЕН ЗА ПОМОЩЬ.

Автор - yuraok
Дата добавления - 25.05.2020 в 21:23

PooHkrd

Дата: Вторник, 26.05.2020, 15:27 | Сообщение № 2

Группа: Пользователи

Ранг: Новичок

Сообщений: 27

Репутация: 7 ±

Замечаний: 0% ±

Office 365

Кросс
И там же готовое решение.

Вот горшок пустой, он предмет простой...

Ответить

yuraok

Дата: Вторник, 26.05.2020, 16:48 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 9

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Цитата PooHkrd, 26.05.2020 в 15:27, в сообщении № 2 ()

Кросс
И там же готовое решение.

Спасибо большое. Только не могу понять куда вставлять етот код (или функции). Чайник, никогда с таким не сталкивался...

Ответить

krosav4ig

Дата: Вторник, 26.05.2020, 18:03 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

yuraok, для начала можно почитать Power Query — обзор и обучение
https://github.com/IvanBond/Power-Query-Cheat-Sheet
https://docs.microsoft.com/ru-ru/powerquery-m/index
https://github.com/power-bi/PowerBI-book-ru/blob/master/README.md

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Вторник, 26.05.2020, 18:08

Ответить

yuraok

Дата: Суббота, 30.05.2020, 20:22 | Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 9

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Нужна Ваша помощь, уважаемые форумчане. Выше в кросс - теме уважаемые форумчане Михаил Л и PooHkrd помогли решить поставленную задачу с развертыванием вложенных этементов в горизонтальном порядке при помощи ниже приведенного кода:

[vba]

Код

let
Источник = Xml.Tables(File.Contents("C:\Users\Administrator\Desktop\primer1.xml")),
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Attribute:date", type datetime}}),
    shop = #"Измененный тип"{0}[shop],
    #"Измененный тип1" = Table.TransformColumnTypes(shop,{{"name", type text}, {"company", type text}, {"url", type text}}),
    offers = #"Измененный тип1"{0}[offers],
    offer = offers{0}[offer],
    #"Измененный тип2" = Table.TransformColumnTypes(offer,{{"url", type text}, {"currencyId", type text}, {"price", Int64.Type}, {"categoryId", Int64.Type}, {"picture", type text}, {"name", type text}, {"description", type text}, {"stock_quantity", Int64.Type}, {"Attribute:available", type logical}, {"Attribute:id", type text}, {"Attribute:group_id", Int64.Type}}),
    #"Развернутый элемент param" = Table.ExpandTableColumn(#"Измененный тип2", "param", {"Element:Text", "Attribute:name"}, {"param.Element:Text", "param.Attribute:name"}),

        #"Pivoted Column" = Table.Pivot(
        #"Развернутый элемент param",
        List.Distinct(#"Развернутый элемент param"[#"param.Attribute:name"]),
        "param.Attribute:name",
        "param.Element:Text",
        (t)=>Text.Combine(t,", ") )
in
    #"Pivoted Column"

[/vba]
Но стала задача развернуть в горизонтальном порядке два параметра как показано на скриншоте:

Так как код применяется для
[vba]

Код

#"Развернутый элемент param",

[/vba]
То раскрыть последовательно нельзя так как пишет что нельзя применить для строк с вложенными элементами.

Нужно както сначала развернуть обе вложенные параметры а потом применить код...
Я попробовал чтото сочинить:
[vba]

Код

let
    Источник = Xml.Tables(File.Contents("C:\Users\Administrator\Desktop\Primer2_xml.xml")),
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Attribute:date", type datetime}}),
    shop = #"Измененный тип"{0}[shop],
    #"Измененный тип1" = Table.TransformColumnTypes(shop,{{"name", type text}, {"company", type text}, {"url", type text}}),
    offer = #"Измененный тип1"{0}[offer],
    #"Измененный тип2" = Table.TransformColumnTypes(offer,{{"url", type text}, {"price", Int64.Type}, {"categoryId", Int64.Type}, {"picture", type text}, {"name", type text}, {"description", type text}, {"stock_quantity", Int64.Type}, {"Attribute:available", type logical}, {"Attribute:id", Int64.Type}}),

    #"Развернутый элемент param" = Table.ExpandTableColumn(#"Измененный тип2", "param", {"Element:Text", "Attribute:name"}, {"param.Element:Text", "param.Attribute:name"}),

    #"Развернутый элемент param1" = Table.ExpandTableColumn(#"Развернутый элемент param", "param1", {"Element:Text", "Attribute:name"}, {"param1.Element:Text.1", "param1.Attribute:name.1"}),

    #"Pivoted Column" = Table.Pivot(
        #"Развернутый элемент param",
        List.Distinct(#"Развернутый элемент param"[#"param.Attribute:name"]),
        "param.Attribute:name",
        "param.Element:Text",

     #"Развернутый элемент param1",
        List.Distinct(#"Развернутый элемент param1"[#"param1.Attribute:name.1"]),
        "param1.Attribute:name.1",
        "param1.Element:Text.1",

        (t)=>Text.Combine(t,", ") )
in
    #"Pivoted Column"

[/vba]

Но не работает и пишет ошибку:

Цитата

Expression.Error: Аргументы 9 были переданы функции, которая ожидает значения между 4 и 5.Сведения:
Pattern=
Arguments=List

ПОМОЖЕТ КТО НАПИСАТЬ КОД ДЛЯ РАЗВЕРТЫВАНИЯ 2-Х ВЛОЖЕННЫХ ТАБЛИЦ ПО ГОРИЗОНТАЛИ КАК Я ОПИСЫВАЛ В НАЧАЛЕ ТЕМЫ??
БУДУ ОЧЕНЬ ПРИЗНАТЕЛЕН ЗА ПОМОЩЬ..

Прикрепляю исходный файл который нужно изменить.

Код

[/vba]
Но стала задача развернуть в горизонтальном порядке два параметра как показано на скриншоте:

Так как код применяется для
[vba]

Код

#"Развернутый элемент param",

Код

[/vba]

Но не работает и пишет ошибку:

Цитата

Expression.Error: Аргументы 9 были переданы функции, которая ожидает значения между 4 и 5.Сведения:
Pattern=
Arguments=List

К сообщению приложен файл: Primer2_xml.xml (8.1 Kb)

Сообщение отредактировал yuraok - Суббота, 30.05.2020, 20:27

Ответить

СообщениеНужна Ваша помощь, уважаемые форумчане. Выше в кросс - теме уважаемые форумчане Михаил Л и PooHkrd помогли решить поставленную задачу с развертыванием вложенных этементов в горизонтальном порядке при помощи ниже приведенного кода:

[vba]

Код

[/vba]
Но стала задача развернуть в горизонтальном порядке два параметра как показано на скриншоте:

Так как код применяется для
[vba]

Код

#"Развернутый элемент param",

Код

[/vba]

Но не работает и пишет ошибку:

Цитата

Expression.Error: Аргументы 9 были переданы функции, которая ожидает значения между 4 и 5.Сведения:
Pattern=
Arguments=List

Автор - yuraok
Дата добавления - 30.05.2020 в 20:22

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Развернуть вложенную таблицу-xml в один ряд PowerQuery (Формулы/Formulas)

Страница 1 из 1
1