Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/PowerQuery- Частичная группировка и транспонирование таблицы - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » PowerQuery- Частичная группировка и транспонирование таблицы (Формулы/Formulas)
PowerQuery- Частичная группировка и транспонирование таблицы
SLAVICK Дата: Среда, 30.11.2016, 13:46 | Сообщение № 1
Группа: Модераторы
Ранг: Старожил
Сообщений: 1834
Репутация: 613 ±
Замечаний: 0% ±

2007,2010,2013,2016
Всем привет.
чтоб не читать дальше много текста - сразу уточню суть задачи:
Прошу помочь доделать запрос PowerQuery .

Теперь подробнее:
Столкнулся с проблемой частичного транспонирования.
В чем суть:
Есть запрос PowerQuery по импорту и преобразованию Json файлов... почти все сделал кроме "схлопования с частичным транспонированием"
этот запрос в таблице (на листе "как есть") - в ней помимо остальных столбцов - еще два:
1 - название параметра(Custom.Value.results.specGroups.specGuruModelcard.name)
2 - сам параметр.(Custom.Value.results.specGroups.specGuruModelcard.value)
задача заключается в том что нужно схлопнуть одинаковые строки в таблице, из названий параметров сделать столбцы, и заполнить пересечения строки и названия параметра Параметром.
Как нужно на листе "Как надо".

В принципе задачу решил - через сводную таблицу (на листе "Как надо")- но есть одно НО - на большем количестве данных - CONCATENATEX (у меня сейчас RAW строк получается свыше 500тыс - схлопнутых =73тыс)- загибается :( .
пришлось сделать отдельный макрос для такого преобразования - но хотелось бы сделать все одним запросом через PowerQuery.
В приложении архив с самим файлом и два Json файла для тестирования.
Чтоб файл работал - разархивируйте весь архив в любую папку, потом обновить подключения.
В файле все понятнее yes .
К сообщению приложен файл: JsonGroup.rar(97Kb)


Иногда все проще чем кажется с первого взгляда.
 
Ответить
СообщениеВсем привет.
чтоб не читать дальше много текста - сразу уточню суть задачи:
Прошу помочь доделать запрос PowerQuery .

Теперь подробнее:
Столкнулся с проблемой частичного транспонирования.
В чем суть:
Есть запрос PowerQuery по импорту и преобразованию Json файлов... почти все сделал кроме "схлопования с частичным транспонированием"
этот запрос в таблице (на листе "как есть") - в ней помимо остальных столбцов - еще два:
1 - название параметра(Custom.Value.results.specGroups.specGuruModelcard.name)
2 - сам параметр.(Custom.Value.results.specGroups.specGuruModelcard.value)
задача заключается в том что нужно схлопнуть одинаковые строки в таблице, из названий параметров сделать столбцы, и заполнить пересечения строки и названия параметра Параметром.
Как нужно на листе "Как надо".

В принципе задачу решил - через сводную таблицу (на листе "Как надо")- но есть одно НО - на большем количестве данных - CONCATENATEX (у меня сейчас RAW строк получается свыше 500тыс - схлопнутых =73тыс)- загибается :( .
пришлось сделать отдельный макрос для такого преобразования - но хотелось бы сделать все одним запросом через PowerQuery.
В приложении архив с самим файлом и два Json файла для тестирования.
Чтоб файл работал - разархивируйте весь архив в любую папку, потом обновить подключения.
В файле все понятнее yes .

Автор - SLAVICK
Дата добавления - 30.11.2016 в 13:46
krosav4ig Дата: Среда, 30.11.2016, 17:24 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1340
Репутация: 536 ±
Замечаний: 0% ±

Excel 2007, 2013
Имхо, Table.Pivot должен помочь. Позже вечером отпишусь, щас негде пощупать.
вот вариант "на коленке", может не работать
[vba]
Код
Table.Pivot(Таблица, List.Distinct([Custom.Value.results.specGroups.specGuruModelcard.name]),  "Custom.Value.results.specGroups.specGuruModelcard.name",  "Custom.Value.results.specGroups.specGuruModelcard.value",  each if List.Count(_)>1 then _ else _{0})
[/vba]
а потом разворачивать столбцы, где получился List


(_)Õvõ(_)
 
Ответить
СообщениеИмхо, Table.Pivot должен помочь. Позже вечером отпишусь, щас негде пощупать.
вот вариант "на коленке", может не работать
[vba]
Код
Table.Pivot(Таблица, List.Distinct([Custom.Value.results.specGroups.specGuruModelcard.name]),  "Custom.Value.results.specGroups.specGuruModelcard.name",  "Custom.Value.results.specGroups.specGuruModelcard.value",  each if List.Count(_)>1 then _ else _{0})
[/vba]
а потом разворачивать столбцы, где получился List

Автор - krosav4ig
Дата добавления - 30.11.2016 в 17:24
SLAVICK Дата: Среда, 30.11.2016, 23:16 | Сообщение № 3
Группа: Модераторы
Ранг: Старожил
Сообщений: 1834
Репутация: 613 ±
Замечаний: 0% ±

2007,2010,2013,2016
Table.Pivot должен помочь

таки да - помог :) (а я ведь и его смотрел, но не сразу понял как вывести текст- думал он для чисел только), но если
потом разворачивать столбцы, где получился List

то криво выходит - оно добавляет строки опять :o .
Хотел прикрутить Text.Combine сразу в строку #"Сведенный столбец"... но чет не вышло - пришлось опять костыли ставить.
Пока получилось так
[vba]
Код
let
    Источник = Folder.Files(GetValue("pathName")),
    #"Другие удаленные столбцы" = Table.SelectColumns(Источник,{"Name", "Folder Path"}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Другие удаленные столбцы", "Custom", each loadJson([Folder Path], [Name])),
    #"Развернутый элемент Custom" = Table.ExpandTableColumn(#"Добавлен пользовательский объект", "Custom", {"Value.results.id", "Value.results.vendor.id", "Value.results.vendor.name", "Value.results.shortName", "Value.results.titles.raw", "Value.results.offers.count", "Value.results.isNew", "Value.results.parentId", "Value.results.specGroups.specGuruModelcard.name", "Value.results.specGroups.specGuruModelcard.value", "Value.total"}, {"Custom.Value.results.id", "Custom.Value.results.vendor.id", "Custom.Value.results.vendor.name", "Custom.Value.results.shortName", "Custom.Value.results.titles.raw", "Custom.Value.results.offers.count", "Custom.Value.results.isNew", "Custom.Value.results.parentId", "Custom.Value.results.specGroups.specGuruModelcard.name", "Custom.Value.results.specGroups.specGuruModelcard.value", "Custom.Value.total"}),
    #"Объединенные столбцы" = Table.CombineColumns(#"Развернутый элемент Custom",{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Сведено"),
    #"Сведенный столбец" = Table.Pivot(#"Объединенные столбцы", List.Distinct(#"Объединенные столбцы"[Custom.Value.results.specGroups.specGuruModelcard.name]),
"Custom.Value.results.specGroups.specGuruModelcard.name", "Custom.Value.results.specGroups.specGuruModelcard.value",  each if List.Count(_)>1 then _ else _ {0}),
    #"Добавлен пользовательский объект1" = Table.AddColumn(#"Сведенный столбец", "Custom", each Text.Combine([xxx], "|")),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект1",{"xxx"})
in
    #"Удаленные столбцы"
[/vba]
завтра попробую на реальных данных. Спасибо + в репу :D .
К сообщению приложен файл: GetJson1.xlsm(62Kb)


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
Table.Pivot должен помочь

таки да - помог :) (а я ведь и его смотрел, но не сразу понял как вывести текст- думал он для чисел только), но если
потом разворачивать столбцы, где получился List

то криво выходит - оно добавляет строки опять :o .
Хотел прикрутить Text.Combine сразу в строку #"Сведенный столбец"... но чет не вышло - пришлось опять костыли ставить.
Пока получилось так
[vba]
Код
let
    Источник = Folder.Files(GetValue("pathName")),
    #"Другие удаленные столбцы" = Table.SelectColumns(Источник,{"Name", "Folder Path"}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Другие удаленные столбцы", "Custom", each loadJson([Folder Path], [Name])),
    #"Развернутый элемент Custom" = Table.ExpandTableColumn(#"Добавлен пользовательский объект", "Custom", {"Value.results.id", "Value.results.vendor.id", "Value.results.vendor.name", "Value.results.shortName", "Value.results.titles.raw", "Value.results.offers.count", "Value.results.isNew", "Value.results.parentId", "Value.results.specGroups.specGuruModelcard.name", "Value.results.specGroups.specGuruModelcard.value", "Value.total"}, {"Custom.Value.results.id", "Custom.Value.results.vendor.id", "Custom.Value.results.vendor.name", "Custom.Value.results.shortName", "Custom.Value.results.titles.raw", "Custom.Value.results.offers.count", "Custom.Value.results.isNew", "Custom.Value.results.parentId", "Custom.Value.results.specGroups.specGuruModelcard.name", "Custom.Value.results.specGroups.specGuruModelcard.value", "Custom.Value.total"}),
    #"Объединенные столбцы" = Table.CombineColumns(#"Развернутый элемент Custom",{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Сведено"),
    #"Сведенный столбец" = Table.Pivot(#"Объединенные столбцы", List.Distinct(#"Объединенные столбцы"[Custom.Value.results.specGroups.specGuruModelcard.name]),
"Custom.Value.results.specGroups.specGuruModelcard.name", "Custom.Value.results.specGroups.specGuruModelcard.value",  each if List.Count(_)>1 then _ else _ {0}),
    #"Добавлен пользовательский объект1" = Table.AddColumn(#"Сведенный столбец", "Custom", each Text.Combine([xxx], "|")),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект1",{"xxx"})
in
    #"Удаленные столбцы"
[/vba]
завтра попробую на реальных данных. Спасибо + в репу :D .

Автор - SLAVICK
Дата добавления - 30.11.2016 в 23:16
krosav4ig Дата: Четверг, 01.12.2016, 04:28 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1340
Репутация: 536 ±
Замечаний: 0% ±

Excel 2007, 2013
а у мну вот так получилось :) [vba]
Код

let
    ExpandAll = (TableToExpand as table, optional ColumnNumber as number) as table =>
    let
        ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
        ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
        ColumnContents = Table.Column(TableToExpand, ColumnName),
        ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))),
        NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
        CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
        ExpandedTable = if CanExpandCurrentColumn then Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames) else TableToExpand,
        NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1
    in if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else fn{0}(ExpandedTable, NextColumnNumber),
    col = [results={"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"},vendor={"id", "name"},specGroups="specGuruModelcard",
               name="results.specGroups.specGuruModelcard.name",value="results.specGroups.specGuruModelcard.value"],
    fn = {
        ExpandAll, //callback для рекурсии
        each {[file=[Folder Path]&[Name],results=Table.FromRecords(Json.Document([Content])[productModsSearch][search][results]),
            total=Json.Document([Content])[productModsSearch][search][total]]}{0},
        {"results",each Table.TransformColumns(Table.SelectColumns(_,col[results]),{{"vendor",each Table.FromRecords({Record.SelectFields(_,col[vendor])})},
            {"specGroups",each Table.TransformColumns(Table.SelectColumns(Table.FromRecords(_),col[specGroups]),{"specGuruModelcard",Table.FromRecords})},
            {"offers",each Table.FromRecords({_})},{"titles",each Table.FromRecords({_})}})}
    },
    step1 = ExpandAll(Table.TransformColumns(Table.FromRecords(Table.TransformRows(Folder.Files(GetValue("pathName")),fn{1})),fn{2})),
    step2 = Table.ReplaceValue(step1,"",null,Replacer.ReplaceValue,{col[value]}),
    step3 = Table.Pivot(step2, List.Distinct(Table.Column(step2,col[name])),col[name],col[value],each Text.Combine(_,"|"))
in
    step3
[/vba]


(_)Õvõ(_)

Сообщение отредактировал krosav4ig - Четверг, 01.12.2016, 04:31
 
Ответить
Сообщениеа у мну вот так получилось :) [vba]
Код

let
    ExpandAll = (TableToExpand as table, optional ColumnNumber as number) as table =>
    let
        ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
        ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
        ColumnContents = Table.Column(TableToExpand, ColumnName),
        ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))),
        NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
        CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
        ExpandedTable = if CanExpandCurrentColumn then Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames) else TableToExpand,
        NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1
    in if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else fn{0}(ExpandedTable, NextColumnNumber),
    col = [results={"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"},vendor={"id", "name"},specGroups="specGuruModelcard",
               name="results.specGroups.specGuruModelcard.name",value="results.specGroups.specGuruModelcard.value"],
    fn = {
        ExpandAll, //callback для рекурсии
        each {[file=[Folder Path]&[Name],results=Table.FromRecords(Json.Document([Content])[productModsSearch][search][results]),
            total=Json.Document([Content])[productModsSearch][search][total]]}{0},
        {"results",each Table.TransformColumns(Table.SelectColumns(_,col[results]),{{"vendor",each Table.FromRecords({Record.SelectFields(_,col[vendor])})},
            {"specGroups",each Table.TransformColumns(Table.SelectColumns(Table.FromRecords(_),col[specGroups]),{"specGuruModelcard",Table.FromRecords})},
            {"offers",each Table.FromRecords({_})},{"titles",each Table.FromRecords({_})}})}
    },
    step1 = ExpandAll(Table.TransformColumns(Table.FromRecords(Table.TransformRows(Folder.Files(GetValue("pathName")),fn{1})),fn{2})),
    step2 = Table.ReplaceValue(step1,"",null,Replacer.ReplaceValue,{col[value]}),
    step3 = Table.Pivot(step2, List.Distinct(Table.Column(step2,col[name])),col[name],col[value],each Text.Combine(_,"|"))
in
    step3
[/vba]

Автор - krosav4ig
Дата добавления - 01.12.2016 в 04:28
SLAVICK Дата: Четверг, 01.12.2016, 10:54 | Сообщение № 5
Группа: Модераторы
Ранг: Старожил
Сообщений: 1834
Репутация: 613 ±
Замечаний: 0% ±

2007,2010,2013,2016
а у мну вот так получилось :)

а у мну не получилось это привязать (может еще не проснулся)- "пишет в ходе вычислений обнаружена циклическая ссылка." :o .

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


Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Четверг, 01.12.2016, 11:04
 
Ответить
Сообщение
а у мну вот так получилось :)

а у мну не получилось это привязать (может еще не проснулся)- "пишет в ходе вычислений обнаружена циклическая ссылка." :o .

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

Автор - SLAVICK
Дата добавления - 01.12.2016 в 10:54
krosav4ig Дата: Четверг, 01.12.2016, 11:57 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1340
Репутация: 536 ±
Замечаний: 0% ±

Excel 2007, 2013
всегда пожалуйста :)
нужно было функцию создать

странно, у меня в обычном запросе нормально отрабатывает
создаю пустой запрос (Из других источников>Пустой запрос), пихаю это все в расширенный редактор и все отрабатывает без ошибок


(_)Õvõ(_)
 
Ответить
Сообщениевсегда пожалуйста :)
нужно было функцию создать

странно, у меня в обычном запросе нормально отрабатывает
создаю пустой запрос (Из других источников>Пустой запрос), пихаю это все в расширенный редактор и все отрабатывает без ошибок

Автор - krosav4ig
Дата добавления - 01.12.2016 в 11:57
SLAVICK Дата: Четверг, 01.12.2016, 16:10 | Сообщение № 7
Группа: Модераторы
Ранг: Старожил
Сообщений: 1834
Репутация: 613 ±
Замечаний: 0% ±

2007,2010,2013,2016
пустой запрос (Из других источников>Пустой запрос), пихаю это все в расширенный редактор и

а у меня вот так вот:

а вот если на этом запросе потом клацнуть - создать функцию - и потом ее вызвать - нормально.
а офис какой? у мну 2016- может поэтому?
К сообщению приложен файл: 6161581.jpg(33Kb)


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
пустой запрос (Из других источников>Пустой запрос), пихаю это все в расширенный редактор и

а у меня вот так вот:

а вот если на этом запросе потом клацнуть - создать функцию - и потом ее вызвать - нормально.
а офис какой? у мну 2016- может поэтому?

Автор - SLAVICK
Дата добавления - 01.12.2016 в 16:10
krosav4ig Дата: Четверг, 01.12.2016, 16:53 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1340
Репутация: 536 ±
Замечаний: 0% ±

Excel 2007, 2013
А у мну 2013, да еще и без DAX Studio. Видимо, тут собака зарыта


(_)Õvõ(_)
 
Ответить
СообщениеА у мну 2013, да еще и без DAX Studio. Видимо, тут собака зарыта

Автор - krosav4ig
Дата добавления - 01.12.2016 в 16:53
SLAVICK Дата: Четверг, 01.12.2016, 17:53 | Сообщение № 9
Группа: Модераторы
Ранг: Старожил
Сообщений: 1834
Репутация: 613 ±
Замечаний: 0% ±

2007,2010,2013,2016
Начал проверять на полном массиве.
Ваша функция спотыкается на файлах - в приложении. Моя с костылями идет дальше - просто оставляя пустое значение .
что нужно добавить (или поменять) - чтоб тоже пропускало? %) .
К сообщению приложен файл: 55.rar(15Kb) · 6220623.jpg(16Kb)


Иногда все проще чем кажется с первого взгляда.
 
Ответить
СообщениеНачал проверять на полном массиве.
Ваша функция спотыкается на файлах - в приложении. Моя с костылями идет дальше - просто оставляя пустое значение .
что нужно добавить (или поменять) - чтоб тоже пропускало? %) .

Автор - SLAVICK
Дата добавления - 01.12.2016 в 17:53
krosav4ig Дата: Пятница, 02.12.2016, 00:55 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1340
Репутация: 536 ±
Замечаний: 0% ±

Excel 2007, 2013
[vba]
Код
Table.FromRecords(Json.Document([Content])[productModsSearch][search][results])
[/vba]меняем на [vba]
Код
Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_})))
[/vba]
[vba]
Код
Table.SelectColumns(_,col[results])
[/vba] меняем на[vba]
Код
Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]}))
[/vba]
[vba]
Код
let
    ExpandAll = (TableToExpand as table, optional ColumnNumber as number) as table =>
    let
        ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
        ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
        ColumnContents = Table.Column(TableToExpand, ColumnName),
        ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))),
        NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
        CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
        ExpandedTable = if CanExpandCurrentColumn then Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames) else TableToExpand,
        NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1
    in if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else fn{0}(ExpandedTable, NextColumnNumber),
    col = [results={"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"},vendor={"id", "name"},specGroups="specGuruModelcard",
            name="results.specGroups.specGuruModelcard.name",value="results.specGroups.specGuruModelcard.value"],
    fn = {
        ExpandAll, //callback для рекурсии
        each {[file=[Folder Path]&[Name],results=Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_}))),
            total=Json.Document([Content])[productModsSearch][search][total]]}{0},
        {"results",each Table.TransformColumns(Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]})),{{"vendor",each Table.FromRecords(
            {Record.SelectFields(_,col[vendor])})},{"specGroups",each Table.TransformColumns(Table.SelectColumns(Table.FromRecords(_),col[specGroups]),
            {"specGuruModelcard",Table.FromRecords})},{"offers",each Table.FromRecords({_})},{"titles",each Table.FromRecords({_})}})}
    },
    step1 = ExpandAll(Table.TransformColumns(Table.FromRecords(Table.TransformRows(Folder.Files(GetValue("pathName")),fn{1})),fn{2})),
    step2 = Table.ReplaceValue(step1,"",null,Replacer.ReplaceValue,{col[value]}),
    step3 = Table.Pivot(step2, List.Distinct(Table.Column(step2,col[name])),col[name],col[value],each Text.Combine(_,"|"))
in
    step3
[/vba]


(_)Õvõ(_)

Сообщение отредактировал krosav4ig - Пятница, 02.12.2016, 00:56
 
Ответить
Сообщение[vba]
Код
Table.FromRecords(Json.Document([Content])[productModsSearch][search][results])
[/vba]меняем на [vba]
Код
Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_})))
[/vba]
[vba]
Код
Table.SelectColumns(_,col[results])
[/vba] меняем на[vba]
Код
Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]}))
[/vba]
[vba]
Код
let
    ExpandAll = (TableToExpand as table, optional ColumnNumber as number) as table =>
    let
        ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
        ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
        ColumnContents = Table.Column(TableToExpand, ColumnName),
        ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))),
        NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
        CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
        ExpandedTable = if CanExpandCurrentColumn then Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames) else TableToExpand,
        NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1
    in if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else fn{0}(ExpandedTable, NextColumnNumber),
    col = [results={"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"},vendor={"id", "name"},specGroups="specGuruModelcard",
            name="results.specGroups.specGuruModelcard.name",value="results.specGroups.specGuruModelcard.value"],
    fn = {
        ExpandAll, //callback для рекурсии
        each {[file=[Folder Path]&[Name],results=Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_}))),
            total=Json.Document([Content])[productModsSearch][search][total]]}{0},
        {"results",each Table.TransformColumns(Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]})),{{"vendor",each Table.FromRecords(
            {Record.SelectFields(_,col[vendor])})},{"specGroups",each Table.TransformColumns(Table.SelectColumns(Table.FromRecords(_),col[specGroups]),
            {"specGuruModelcard",Table.FromRecords})},{"offers",each Table.FromRecords({_})},{"titles",each Table.FromRecords({_})}})}
    },
    step1 = ExpandAll(Table.TransformColumns(Table.FromRecords(Table.TransformRows(Folder.Files(GetValue("pathName")),fn{1})),fn{2})),
    step2 = Table.ReplaceValue(step1,"",null,Replacer.ReplaceValue,{col[value]}),
    step3 = Table.Pivot(step2, List.Distinct(Table.Column(step2,col[name])),col[name],col[value],each Text.Combine(_,"|"))
in
    step3
[/vba]

Автор - krosav4ig
Дата добавления - 02.12.2016 в 00:55
SLAVICK Дата: Пятница, 02.12.2016, 13:04 | Сообщение № 11
Группа: Модераторы
Ранг: Старожил
Сообщений: 1834
Репутация: 613 ±
Замечаний: 0% ±

2007,2010,2013,2016
Теперь работает :D , но скорость ...
Сделал замеры:
Цитата
Запрос — d
StartQuery: 02.12.2016 9:47:44
EndQuery: 02.12.2016 9:49:58
Time: 134,926s

Запрос — krosav4ig
StartQuery: 02.12.2016 9:53:11
EndQuery: 02.12.2016 10:36:11
Time: 2580,582s


Мой запрос с костилями отработал за 2минуты - Ваш 43 минуты... видимо расплата за универсальность :o .
Для справки - обработано почти 9000 файлов.
Придется остановится на своем костыльном запросе. СПС :D .


Иногда все проще чем кажется с первого взгляда.
 
Ответить
СообщениеТеперь работает :D , но скорость ...
Сделал замеры:
Цитата
Запрос — d
StartQuery: 02.12.2016 9:47:44
EndQuery: 02.12.2016 9:49:58
Time: 134,926s

Запрос — krosav4ig
StartQuery: 02.12.2016 9:53:11
EndQuery: 02.12.2016 10:36:11
Time: 2580,582s


Мой запрос с костилями отработал за 2минуты - Ваш 43 минуты... видимо расплата за универсальность :o .
Для справки - обработано почти 9000 файлов.
Придется остановится на своем костыльном запросе. СПС :D .

Автор - SLAVICK
Дата добавления - 02.12.2016 в 13:04
krosav4ig Дата: Суббота, 03.12.2016, 01:51 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1340
Репутация: 536 ±
Замечаний: 0% ±

Excel 2007, 2013
43 минуты

а если без авторазворачивания?
мне теперь аж самому интересно стало, какая именно часть запроса тормозит :D
[vba]
Код
let
    col = [results={"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"},vendor={"id", "name"},specGroups="specGuruModelcard",
            name="results.specGroups.specGuruModelcard.name",value="results.specGroups.specGuruModelcard.value"],
    fn = (tbl as table, column as text, columns as list) as table => Table.ExpandTableColumn(tbl, column, columns, List.Transform(columns,each column & "." & _)),
    fn1 = each {[file=[Folder Path]&[Name],results=Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_}))),
            total=Json.Document([Content])[productModsSearch][search][total]]}{0},
    fn2 = {"results",each Table.TransformColumns(Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]})),{{"vendor",each Table.FromRecords(
            {Record.SelectFields(_,col[vendor])})},{"specGroups",each Table.TransformColumns(Table.SelectColumns(Table.FromRecords(_),col[specGroups]),
            {"specGuruModelcard",Table.FromRecords})},{"offers",each Table.FromRecords({_})},{"titles",each Table.FromRecords({_})}})},
    step1 = (Table.TransformColumns(Table.FromRecords(Table.TransformRows(Folder.Files(GetValue("pathName")),fn1)),fn2)),
    step2  = fn(fn(fn(fn(step1, "results", col[results]),"results.vendor", col[vendor]),"results.specGroups", {"specGuruModelcard"}),"results.offers", {"count"}),
    step3 = fn(fn(step2, "results.specGroups.specGuruModelcard", {"name", "value"}),"results.titles", {"raw"}),
    step4 = Table.ReplaceValue(step3,"",null,Replacer.ReplaceValue,{col[value]}),
    step5 = Table.Pivot(step4,List.Distinct(Table.Column(step4,col[name])),col[name],col[value],each Text.Combine(_,"|"))
in
    step5
[/vba]


(_)Õvõ(_)
 
Ответить
Сообщение
43 минуты

а если без авторазворачивания?
мне теперь аж самому интересно стало, какая именно часть запроса тормозит :D
[vba]
Код
let
    col = [results={"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"},vendor={"id", "name"},specGroups="specGuruModelcard",
            name="results.specGroups.specGuruModelcard.name",value="results.specGroups.specGuruModelcard.value"],
    fn = (tbl as table, column as text, columns as list) as table => Table.ExpandTableColumn(tbl, column, columns, List.Transform(columns,each column & "." & _)),
    fn1 = each {[file=[Folder Path]&[Name],results=Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_}))),
            total=Json.Document([Content])[productModsSearch][search][total]]}{0},
    fn2 = {"results",each Table.TransformColumns(Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]})),{{"vendor",each Table.FromRecords(
            {Record.SelectFields(_,col[vendor])})},{"specGroups",each Table.TransformColumns(Table.SelectColumns(Table.FromRecords(_),col[specGroups]),
            {"specGuruModelcard",Table.FromRecords})},{"offers",each Table.FromRecords({_})},{"titles",each Table.FromRecords({_})}})},
    step1 = (Table.TransformColumns(Table.FromRecords(Table.TransformRows(Folder.Files(GetValue("pathName")),fn1)),fn2)),
    step2  = fn(fn(fn(fn(step1, "results", col[results]),"results.vendor", col[vendor]),"results.specGroups", {"specGuruModelcard"}),"results.offers", {"count"}),
    step3 = fn(fn(step2, "results.specGroups.specGuruModelcard", {"name", "value"}),"results.titles", {"raw"}),
    step4 = Table.ReplaceValue(step3,"",null,Replacer.ReplaceValue,{col[value]}),
    step5 = Table.Pivot(step4,List.Distinct(Table.Column(step4,col[name])),col[name],col[value],each Text.Combine(_,"|"))
in
    step5
[/vba]

Автор - krosav4ig
Дата добавления - 03.12.2016 в 01:51
anvg Дата: Суббота, 03.12.2016, 22:11 | Сообщение № 13
Группа: Друзья
Ранг: Форумчанин
Сообщений: 227
Репутация: 119 ±
Замечаний: 0% ±

2010, 2016
Доброе время суток.
Может быстрее будет по структуре Json идти? Код для функции
[vba]
Код
let
    source = Json.Document(File.Contents("c:\Path\Jsons\Modif_1712539764_2")),
    f1 = source[productModsSearch],
    toTable = Table.FromList(f1[search][results], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandedRecs = Table.ExpandRecordColumn(toTable, "Column1", {"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"}, {"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"}),
    transform1 = Table.TransformColumns(expandedRecs, {
    {"titles", each _[raw], type text},
    {"offers", each _[count], Int64.Type},
    {"specGroups", each Table.FromRecords(_)[specGuruModelcard], type list}
    }),
    totalVal = f1[pager][total],
    expandedVendor = Table.ExpandRecordColumn(transform1, "vendor", {"id", "name"}, {"vendor.id", "vendor.name"}),
    expandList = Table.ExpandListColumn(expandedVendor, "specGroups"),
    transform2 = Table.TransformColumns(expandList, {
  {"specGroups", each Table.Group(Table.FromRecords(_), "name", { {"value", each Text.Combine([value], " ")} }), type table}
    }),
    rotated = Table.AddColumn(transform2, "rotated", each Table.FromRows({[specGroups][value]}, [specGroups][name])),
    expandedRotated = Table.ExpandTableColumn(rotated, "rotated", {"Сезонность", "Тип автомобиля", "типоразмер", "xxx", "Технология RunFlat", "Тип зимних шин"}),
    addTotal = Table.AddColumn(expandedRotated, "total", each totalVal),
    return = Table.RemoveColumns(addTotal,{"specGroups"})
in
    return
[/vba]
Успехов.


Сообщение отредактировал anvg - Суббота, 03.12.2016, 22:12
 
Ответить
СообщениеДоброе время суток.
Может быстрее будет по структуре Json идти? Код для функции
[vba]
Код
let
    source = Json.Document(File.Contents("c:\Path\Jsons\Modif_1712539764_2")),
    f1 = source[productModsSearch],
    toTable = Table.FromList(f1[search][results], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandedRecs = Table.ExpandRecordColumn(toTable, "Column1", {"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"}, {"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"}),
    transform1 = Table.TransformColumns(expandedRecs, {
    {"titles", each _[raw], type text},
    {"offers", each _[count], Int64.Type},
    {"specGroups", each Table.FromRecords(_)[specGuruModelcard], type list}
    }),
    totalVal = f1[pager][total],
    expandedVendor = Table.ExpandRecordColumn(transform1, "vendor", {"id", "name"}, {"vendor.id", "vendor.name"}),
    expandList = Table.ExpandListColumn(expandedVendor, "specGroups"),
    transform2 = Table.TransformColumns(expandList, {
  {"specGroups", each Table.Group(Table.FromRecords(_), "name", { {"value", each Text.Combine([value], " ")} }), type table}
    }),
    rotated = Table.AddColumn(transform2, "rotated", each Table.FromRows({[specGroups][value]}, [specGroups][name])),
    expandedRotated = Table.ExpandTableColumn(rotated, "rotated", {"Сезонность", "Тип автомобиля", "типоразмер", "xxx", "Технология RunFlat", "Тип зимних шин"}),
    addTotal = Table.AddColumn(expandedRotated, "total", each totalVal),
    return = Table.RemoveColumns(addTotal,{"specGroups"})
in
    return
[/vba]
Успехов.

Автор - anvg
Дата добавления - 03.12.2016 в 22:11
Мир MS Excel » Вопросы и решения » Вопросы по Excel » PowerQuery- Частичная группировка и транспонирование таблицы (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!