Страницы: 1
RSS
Power Query. Распарсить List в составе сложной структуры json и расставить по местам, (как "ВПР" в соответствии с индексом вхождения в List и по месту номера недели в месяце)
 
частично считала json - но вот с одной колонкой застопорилась...
в PQ опыта пока мало...
мучалась, делала, хотела вопрос задать (как по сути leaves LIST раздвинуть вниз ???)
(по сути nodes LIST - Table.Expand сама уже раздвинула, а с листьями ещё проблемы)...
-- пока вдруг не вспомнила - деревце то странное!!
- отдельно ветка справочной инфо [productIds] - листьями, полагаю...
- и отдельно ветка с текущей инфо [calendarEntries] - ветками, полагаю...
... подумала "приплыли", т.е. ещё и ВПРить согласно индексу вхождения в справочник и по месту соответствия номеру недели в месяце,... решила выложить на форум... у самой пока идей нет...
если кто-нибудь любит такие заковырки и хватает опыта в PQ - черканите please, возможно ли раскрыть list и состыковать (к номеру недели в месяце) через PQ и как??...
по сути, как понимаю: тут мало того, что взять/распарсить тот List из productIds надо, так ещё и как-то отВПР-ить к датам экспирации...
чтобы всё встало на свои места на листе tbl_expand... кое-какие пояснения по логике внутри
p.s.
я пока спотыкаюсь на Table.FromList - не хочет Тип List превращать хотя бы в Текст... ( вообще ни во что его превращать не хочет
p.p.s
пока я не совсем на связи- но всё равно спасибо всем откликнувшимся... для примера в архив приложена и структура дерева... парсинг из другого текстовика в архиве
Изменено: JeyCi - 13.02.2018 14:54:02
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Ничего не понял, переведите кто-то что сделать то надо :)
Развернуть просто нажав на символ с 2 стрелочками не подходит ?
 
Цитата
Илья Демид написал:Развернуть просто нажав на символ с 2 стрелочками не подходит ?
не подходит... нужна плоская таблица - принцип описан максимально полно
Цитата
Илья Демид написал:Ничего не понял
тогда и не теряйте своё время
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
не подходит... нужна плоская таблица
Так если развернуть в строки у вас и будет плоская таблица. Это таблица разворачивается в столбцы, а списки в строки либо в одну ячейку, в которой перечислены все элементы списка через разделитель.
Изменено: PooHkrd - 13.02.2018 15:20:52
Вот горшок пустой, он предмет простой...
 
Доброе время суток.
Цитата
PooHkrd написал:
Так если развернуть в строки у вас и будет плоская таблица
Поддержу коллегу. Пойдём от противного. Пример во вложении, требуется указать, что не так?
 
Цитата
Андрей VG написал:
указать, что не так?
ваш пример ещё не смотрела, но нарисовала "что не так" - если просто expand
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
добавила лист info_list - там показано что из столбца productIds только справочная (!!) инфо - но надо её разложить к field [productCode] - по логике соответствующе - как на картинке (или в файле в комментах) - позиция id в list'e - к соответствующей недели (1,2,3,4,5)... по сути ИНДЕКС(), полагаю... как-то так (в файле в С1 комментом прописана логика)...
Изменено: JeyCi - 14.02.2018 15:38:18
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
JeyCi, хорошо, пусть список PorductIds пронумерован 1..5. Тогда для productIds, где productId = 8440 имеем
8440 - 1
8441 - 2
8442 - 3
8443 - 4
8444 - 5
В списке записей calendarEntries для этого же productId 6 записей. Их тоже нумеровать? И по простому соединять 1 = 1, 2 = 2. Но там два вида productCode WE?G18 и WE?H18, причём после WE в обоих случаях следуют и 1 и 2. Кто приоритетнее? Но, тут хотя бы, если брать порядок следования для lastTrade по датам правильный, чтобы говорить о днях недели.
Как быть с productId 1346? Там есть проблемы с порядком дат в calendarEntries
Изменено: Андрей VG - 14.02.2018 15:58:25
 
Цитата
Андрей VG написал:
Но там два вида productCode WE?G18 и WE?H18
последняя !! буква - это месяц (у них такая аббревиатура).....
2 последние цифры это год
- это всё не важно...
но у недельных [productCode] где-то в начале или в середине (где как) есть ещё цифра номера недели... у месячных её нет...
и этот номер недели соответствует порядку местоположения в справочном List - [productIds]...
вот такие премудрости... кстати недельные помечены ИСТИНА в поле [weekly] - как отмечала...
p.s.
если контракт ещё не открыт - то его может и не быть ещё в природе в [productCode]... поэтому не все недели должны/могут быть в ветке текущих торгуемых - [productCode]... а в справочнике [productIds] - прописаны все id справочно
Изменено: JeyCi - 14.02.2018 16:25:12
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
но у недельных [productCode] где-то в начале или в середине (где как) есть ещё цифра номера недели... у месячных её нет...и этот номер недели соответствует порядку местоположения в справочном List - [productIds]...
То есть по примеру выводим 8440 с записями productCode, равными WE1G18 и WE1H18, так как у них есть 1?
А для месячных что выводить?
Изменено: Андрей VG - 14.02.2018 16:12:07
 
Цитата
Андрей VG написал:
8440 с записями productCode, равными WE1G18 и WE1H18
вот именно оно так и есть  :) ...
только как выводить не понятно... наверно, очень сложно... промаркировать всё по месту productCode из справочника productIds... при этом выгрузив всё в плоскую таблицу...
поэтому публикую на форуме - кто любит головоломки... сама пока в раздумьях
Изменено: JeyCi - 14.02.2018 16:16:10
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
Андрей VG написал:
А для месячных что выводить?
там у них List состоит только из одного значения... одно для всех - для конкретного типа calendarEntries ветка вся... там в 1-м посте в архиве есть развёртка всего json - видны ветки
Изменено: JeyCi - 14.02.2018 16:19:00
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
Андрей VG написал:
И по простому соединять 1 = 1, 2 = 2.
не всегда справочник состоит из последовательных чисел (как в примере 8440 и т.д.) ... но позиция в справочнике соответствует номеру недели (по порядку)
Изменено: JeyCi - 14.02.2018 16:31:10
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
там у них List состоит только из одного значения.
А как же productId = 269?
 
Цитата
Андрей VG написал:
как же productId = 269?
это последняя строка с 1st_level? сама только потом его заметила - поправила info_list ( там 1,2,3,4,5 недели - A,B,C,D,E значения) - коммент яч D1... они недельные - у них ИСТИНА в [weekly]...
во вложении на лист test также выделила отдельно [product] !! (без буквы месяца, которая последняя, и 2х цифр года) (там для 269 видно - последняя буква - это неделя) и  [unix] столбец месяц-год выделила... там в PQ пометила свою точку зрения в комментах - видимо UDFку надо делать, чтобы в столбец ID вытягивать из Справочника нужный ID... только пока ещё не получилось  8)  
Изменено: JeyCi - 15.02.2018 16:12:40
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
недели - A,B,C,D,E
Там нет Буковки B at all ;)
А по productId = 59? Да, пока значение в productIds - одно, но буковки то третьи в calendarEtries разные G, H, J, K N.
Может всё таки поставщика данных запросить, а что это вы это там наделали?
 
Цитата
Андрей VG написал:
Там нет Буковки B at all  

так 2й недели пока нет в текущих at all - на эту неделю возможно выпадает экспирация месячного...  8)
Андрей VG спасибо большое - у вас получилось... List превратить в Table...UDFкой  transformed... и сджойнить (по ключу, как поняла)... ключи WL (порядк. номер справочного List'а) и WR (номер недели из [productCode])...

- там кстати delYear можно сразу и delMonth (1 последняя буква) - т.е. в формуле delYear (-3) вместо (-2)...
[или эту букву вынести в отдельную колонку и 2 последние цифра ещё в одну - сама уже вынесу, если понадобится - там в unix я в принципе месяц и год пометила и без аббревиатуры - потом разберусь как лучше - если общий ключ из product-month-year понадобится когда-нибудь потом - это мелочь]...

и я вот думаю, насчёт 269... - тогда получается логику можно использовать:
если [weekly] и нет digits - то из всего имеющегося (6 строк в calendarEntries сейчас) удалить общую часть [или Replace на ""]
(в данном случае она будет идентифицирована как "VT" - только функцию для определения общего надо, если это возможно ???)...
и оставшиеся буквы даже Table.TransformColumns({A,B,C,D}{1,2,3,4,5}) можно - их ведь всего 5 вариаций - не много... больше недель в месяце не будет))спасибо!!!... буду дальше мучить новый язык

P.S. вот кстати попробовала на других календарях... всё работает...
только ещё проверку - If [calendarEntry] пустой Then не выводить Record - как-то ещё прописать бы... прикладываю json-структуру и её дерево такой ситуации (побочной)... если будет время... они оказывается зачем-то ещё пустой [calendarEntry] формируют - пока не знаю, для каких случаев

Изменено: JeyCi - 16.02.2018 11:47:58
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Андрей VG спасибо ещё раз - подправила, что хотела - на свой вкус - взяла на вооружение ваши идеи (и ещё одну вашей руки из другой ветки - нашла у себя на компе) - получила, что хотела... просто там нюансы по Австралу и Канадцу были - алгоритмически обошла, как в файле коде (файл не проходит по размеру) - прикладываю - тестовые календари по австралу и канадцу)... в любом случае - пока ТОЛЬКО 1 символ отвечает за неделю...
p.s. и в личку вам выслала ответ на ваши вопросы
p.p.s вобщем тему считаю исчерпанной - не всегда могу быть на связи... всем спасибо!!! большое  :)
Код
let
    Источник = Json.Document(File.Contents(Excel.CurrentWorkbook(){[Name="fPath"]}[Content]{0}[Column1])),
    subTable = Table.FromRecords(Источник),

diff  = (me as number, col as list) =>
let
    to = Table.FromColumns(List.Transform(col,each Text.ToList(_))),
    filtered = Table.Group(to, Table.ColumnNames(to), { {"unique", each _{0}} })[[unique]],        // !!!
    rec = Table.TransformColumns(filtered, {"unique", each Record.FieldValues(_)}),              // list of transversal characters from [product-tk] column  
    wklist = Table.FirstValue(Table.SelectRows(rec, each List.Count(List.Distinct([unique])) > 1)),        // ?!1!? row of different values
    wk = Text.ToList(wklist{me}),    //  (posenum - me - current of it)
    digits = {"A".."E"},
    done = if List.Count(List.Intersect({wk, digits}))=1 then 
        let
            step1=List.ReplaceMatchingItems(wk,{{"A",1},{"B",2},{"C",3},{"D",4},{"E",5}}),
            step2=Number.From(step1{0})
        in step2
    else Number.From(wk{0})
in done,

    transformed = Table.TransformColumns(subTable, {
        {"productIds", (items) => Table.FromRows(List.Transform(List.Positions(items), each {items{_}, (_ + 1)}), {"productIds", "WL"}), Table.Type},    // !!!
 {"calendarEntries", (recs) =>        
      Table.RemoveColumns(
                Table.AddColumn(   
                    Table.TransformColumns(
                        Table.AddColumn(Table.FromRecords(recs), "mny", each Text.Range([productCode],  Text.Length([productCode]) - 3,3), List.Type)        // "mn-year"
                    ,{"productCode", (value)=>Text.Range(value,0,Text.Length(value) - 3)}),                         // "tk"
         "wk", each  
                        let
                            tt = Table.Column(Table.TransformColumns(Table.FromRecords(recs),{"productCode", (value)=>Text.Range(value,0,Text.Length(value) - 3)}),"productCode"),    // list of column values
                            pos = List.PositionOf(tt, Text.From([productCode])),
                            w = if List.Count(List.Distinct(tt))=1 then 1 else 
                                let 
                                    nn = diff(pos, tt)                       // "wk"
                                in nn
                        in w
                 , Number.Type),
            {"firstTrade", "lastTrade"}),
        Table.Type}
    }),
    joined = Table.AddColumn(transformed, "joined", each Table.Join([productIds], {"WL"}, [calendarEntries], {"wk"}), Table.Type),                                // !!!   
   
    #"Removed Columns" = Table.RemoveColumns(joined,{"productIds", "calendarEntries"}),   
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns", {"joined"}),   
    #"Expanded {0}" = Table.ExpandTableColumn(#"Removed Errors", "joined", {"productIds", "contractMonth", "settlement", "productCode", "mny",  "expirationDate"}, {"productIds", "contractMonth", "settlement", "productCode", "mny", "expirationDate"}),   
    product=Table.RenameColumns(#"Expanded {0}", {{"productCode", "tk"}, {"expirationDate", "expire"}}),        //, MissingField.Ignore   
    unixcol = Table.TransformColumns(product, {"expire", (value)=> Text.Upper(Date.ToText(Date.From((Value.FromText(value)/86400000)+25569), "MMMyy", "en-US"))}),        // "expirationDate" UNIX   
       
    main = Table.AddIndexColumn(unixcol, "№п/п", 1),   
    #"Removed Columns1" = Table.RemoveColumns(main,{"name", "productId", "daily", "sto", "contractMonth"}),   
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"№п/п", "label", "optionType", "weekly", "productIds",  "settlement", "tk", "mny", "expire"}),   
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"weekly", type logical}, {"settlement", type date}})   
in   
    #"Changed Type"
Изменено: JeyCi - 11.03.2018 10:28:18
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
и просто справочник для сверки такой
Код
let
    Источник = Json.Document(File.Contents(Excel.CurrentWorkbook(){[Name="fPath"]}[Content]{0}[Column1])),
    subTable = Table.FromRecords(Источник),
        transformed = Table.TransformColumns(subTable, {
        {"productIds", (items) => Table.FromRows(List.Transform(List.Positions(items), each {items{_}, (_ + 1)}), {"productIds", "wk"}), Table.Type}
}),
    #"Expanded productIds" = Table.ExpandListColumn(transformed, "productIds"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded productIds",{"productId", "daily", "sto", "calendarEntries", "optionType"}),
    #"Expanded productIds1" = Table.ExpandRecordColumn(#"Removed Columns", "productIds", {"productIds", "wk"}, {"productId", "wk"})
in
    #"Expanded productIds1"
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
и в личку вам выслала ответ на ваши вопросы
Спасибо. Вот теперь можно по чётким определениям строить полноценный вывод. Рад, что у вас всё получилось.
 
сделала файл - тоже выложу...
***
кстати поняла, в чём у меня была путаница в прочтении синтаксиса когда-то: (стоит обратить внимание, насколько поняла)
в json - [...] - это массив, а {...} - это record в системе {field : value}
а на M - [...] - ЭТО record, а {...} - это list (по сути, полагаю, можно сказать массив элементов)

- как только обратила на это внимание - легче стало подобрать нужные методы для обращения к структуре json...
***
PQ даёт возможность (и надо ей пользоваться) - оперировать Агрегатами элементов!.. хотя синтаксис языка M вынуждает иногда жонглировать логическими последовательностями ввиду нехватки в языке словоформ... а может, пока ввиду не полного моего знакомство с языком...
но на примерах с форума - получилась у меня так достаточно универсально исходя из моих реалий (environment & variables)... рада, что удалось обыграть и Австрал и Канадца (развернув мой view по столбцу на 90градусов по каждой букве тикера и отобрав столбец с различиями)...
***
думала - может как-то AntiJoin? - интересный новый Join в M - но тоже есть немного несовершенство синтаксиса в языке - можно только 2 таблицы по ключу... а так хотелось все Rows с tk превратить в Tables посимвольно и сджойнить всё оптом (количество Rows вариабельно) - но пока M не даёт такой возможности с точки зрения методов и их синтаксиса (джойнить вариабельное количество таблиц) - полагаю по причине природы Баз Данных (они не подразумевают много таблиц) - приходится искать обходные алгоритмические манёвры... хорошо, что они находятся! на форуме
Изменено: JeyCi - 16.07.2021 07:06:00
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
сджойнить всё оптом
в любом случае это последовательность джойнов, которую можно создать при помощи List.Generate или List.Accumulate
F1 творит чудеса
 
Максим Зеленский спасибо, подумаю... !!
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
удалось получить ускорение работы этого кода в составе более сложного - через использование Table.Buffer для таблицы с распарсенными строками И забуферивания также сложной трансвормации колонок... выводы тестирования
Изменено: JeyCi - 10.08.2018 09:32:14
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Страницы: 1
Наверх