Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PowerQuery Развернуть столбцы с данными из json
 
#1
21.02.2023 16:10:22
Добрый день, уважаемые.
Для описания проблему объясню что я делаю и что я хочу получить, надеюсь это поможет с решением проблемы.
Я выгружаю данные по api с сайта, они приходят в формате json и превращаются в таблицу с которой я уже и работаю.
На этом этапе возникла проблема. Ключ payments имеет внутри словарь с типами оплаты. А сами словари внутри этого словаря имеют уникальный id. В примере это 21507 и 21508. Именно из за того что эти словари внутри имеют отличное друг от друга название, я не могу(ввиду своей неопытности в powerquery) развернуть в столбцы. Максимум словарей внутри payments может быть 5. Отсюда вытекает задача:
Необходимо добавить в таблицу 5 столбцов в которые будут загружаться вложенные словари в payments. Если внутри словаря 2 типа оплаты - заполнять первые 2 столбца которые мы создали ранее, если их все 5 - заполняем все 5 столбцов. Из этих столбцов я уже смогу вытащить необходимые мне данные(способ, статус и сумму)
Прикладываю json с обезличенными данными.  
 
 
 
#2
21.02.2023 17:20:44
https://www.youtube.com/watch?v=R5CYPsMwY-o
 
 
 
#3
21.02.2023 17:36:33
Цитата
написал:
https://www.youtube.com/watch?v=R5CYPsMwY-o
Я умею доставать данные из json в таблицу, мне необходимо решить проблему со списками в payments.
Но за видео спасибо.
 
 
 
#4
21.02.2023 18:05:04
Артем Есьман, покажите как есть, и как надо
да, у меня ваш файл json не открывается, приложите другой
 
 
 
#5
21.02.2023 18:13:28
Цитата
написал:
у меня ваш файл json не открывается,
аналогично. Ошибка  
Цитата
DataFormat.Error: Duplicate name 'totalSumm'.
Пришелец-прораб.
 
 
 
#6
21.02.2023 18:27:27
jakim, зачем через "Ж"? Excel может импортировать файлы формата json. Ну, может быть, не во всех версиях...
Пришелец-прораб.
 
 
 
#7
22.02.2023 10:24:57
Цитата
написал:
Артем Есьман, покажите как есть, и как надо
да, у меня ваш файл json не открывается, приложите другой
Как оно есть: payments есть словари с разными именами. В каждой последующей ячейке уникальный id способа оплаты отличается от предыдущего.
Как оно надо: развернуть словари и развернуть необходимые данные(статус, тип и сумму) из каждого вложенного в payments словаря.
ошибку с повторением totalsumm убрал - json во вложении
 
 
 
 
#8
22.02.2023 10:46:09
Артем Есьман, проблемы те же
 
 
 
#9
22.02.2023 11:54:08
Цитата
написал:
Артем Есьман, проблемы те же
Исправил, проверил, теперь точно работает  
 
 
 
#10
22.02.2023 13:15:08
пусть будет вот так вот... 5 раз прочитал, все равно не понял, что именно надо
Код
let
    a = Table.FromList(Json.Document(Web.Contents("https://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=516275&action=download"))[orders], (x)=>{x}),
    b = Table.ExpandRecordColumn(a, "Column1", {"id", "payments"}),
    c = Table.ExpandRecordColumn(b, "payments", {"21507", "21508", "21532131"}),
    d = Table.UnpivotOtherColumns(c, {"id"}, "q", "w"),
    q = Table.ExpandRecordColumn(d, "w", {"status", "type", "amount"})
in
    q
 
 
 
#11
22.02.2023 13:16:19
Артем Есьман, я взял на себя смелость предложить, что вам не надо раздвигать таблицу вправо. Достаточно положить в отдельную колонку последний id (кажется, payment id) и все укладывается в достаточно симпатичную таблицу. При этом в последней записи дублируется этот самый payment_id. Ну это если я чего-то с чем-то не спутал.
Код
let
    Source = Json.Document(File.Contents("your_path\temp-2.txt"))[orders],
    a = Table.FromRows(List.Transform(Source, Record.ToList), {"rec_id", "rec"}),
    b = 
        Table.TransformColumns(
            a, 
            {"rec", each List.Zip({Record.FieldNames(_) , Record.ToList(_)})}
        ),
    c = Table.TransformColumns(b, {"rec", each Table.FromRows(_, {"payment_id", "data"})}),
    expand_rec = Table.ExpandTableColumn(c, "rec", {"payment_id", "data"}, {"payment_id", "data"}),
    expand_data = Table.ExpandRecordColumn(expand_rec, "data", {"id", "status", "type", "externalId", "amount", "paidAt"}, {"id", "status", "type", "externalId", "amount", "paidAt"})
in
    expand_data
 
Пришелец-прораб.
 
 
 
#12
22.02.2023 13:50:02
Цитата
написал:
Артем Есьман, я взял на себя смелость предложить, что вам не надо раздвигать таблицу вправо. Достаточно положить в отдельную колонку последний id (кажется, payment id) и все укладывается в достаточно симпатичную таблицу. При этом в последней записи дублируется этот самый payment_id. Ну это если я чего-то с чем-то не спутал.  
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8  9  10  11  12  13      let          Source = Json.Document(File.Contents(  "your_path\temp-2.txt"  ))[orders],          a = Table.FromRows(List.Transform(Source, Record.ToList), {  "rec_id"  ,   "rec"  }),          b =               Table.TransformColumns(                  a,                   {  "rec"  , each List.Zip({Record.FieldNames(_) , Record.ToList(_)})}              ),          c = Table.TransformColumns(b, {  "rec"  , each Table.FromRows(_, {  "payment_id"  ,   "data"  })}),          expand_rec = Table.ExpandTableColumn(c,   "rec"  , {  "payment_id"  ,   "data"  }, {  "payment_id"  ,   "data"  }),          expand_data = Table.ExpandRecordColumn(expand_rec,   "data"  , {  "id"  ,   "status"  ,   "type"  ,   "externalId"  ,   "amount"  ,   "paidAt"  }, {  "id"  ,   "status"  ,   "type"  ,   "externalId"  ,   "amount"  ,   "paidAt"  })    in          expand_data  
   
Почти то что надо, но если словарей в payments больше или меньше двух то эти значения не попадают в таблицу
 
 
 
#13
22.02.2023 13:52:21
Цитата
Цитата
написал:
пусть будет вот так вот... 5 раз прочитал, все равно не понял, что именно надо
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8      let          a = Table.FromList(Json.Document(Web.Contents(  " https://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=516275&action=download "  ))[orders], (x)=>{x}),          b = Table.ExpandRecordColumn(a,   "Column1"  , {  "id"  ,   "payments"  }),          c = Table.ExpandRecordColumn(b,   "payments"  , {  "21507"  ,   "21508"  ,   "21532131"  }),          d = Table.UnpivotOtherColumns(c, {  "id"  },   "q"  ,   "w"  ),          q = Table.ExpandRecordColumn(d,   "w"  , {  "status"  ,   "type"  ,   "amount"  })    in          q  
 
Работает, но если словарей больше или меньше двух -- эти данные не попадают в таблицу. Вложенных в payments словарей может быть от 1 до 5
 
 
 
#14
22.02.2023 13:58:12
Цитата
написал:
Почти то что надо
Не понял что не так. Какие "эти значения"? Может надо убрать лишние данные? Подкорректируйте названия колонок, которые "вытягиваются" на последнем шаге.
Вон выше Антон сделал чистенький код. Там ничего лишнего нет (убрать бы только привязку к payment_id {"21507", "21508", "21532131"}, но это не сложно, наверное).  
Пришелец-прораб.
 
 
 
#15
22.02.2023 13:59:57
Цитата
написал:
если словарей больше или меньше двух
Я вроде не привязывался к кол-ву словарей. Потом, в одной из записей - один словарь. Он же попал.
upd: выкладывайте json с примером, когда словари не попадают в таблицу.
Изменено: Alien Sphinx - 22.02.2023 14:01:23
Пришелец-прораб.
 
 
 
#16
22.02.2023 14:21:43
Цитата
написал:
let    Source = Json.Document(File.Contents("your_path\temp-2.txt"))[orders],    a = Table.FromRows(List.Transform(Source, Record.ToList), {"rec_id", "rec"}),    b =         Table.TransformColumns(            a,             {"rec", each List.Zip({Record.FieldNames(_) , Record.ToList(_)})}        ),    c = Table.TransformColumns(b, {"rec", each Table.FromRows(_, {"payment_id", "data"})}),    expand_rec = Table.ExpandTableColumn(c, "rec", {"payment_id", "data"}, {"payment_id", "data"}),    expand_data = Table.ExpandRecordColumn(expand_rec, "data", {"id", "status", "type", "externalId", "amount", "paidAt"}, {"id", "status", "type", "externalId", "amount", "paidAt"})in    expand_data
Перепроверил все еще раз, оказалось проблема была не в коде.
Спасибо!

Можно еще попросить объяснить как убрать привязку к id?
Цитата
написал:
Цитата
написал:
Почти то что надо
Вон выше Антон сделал чистенький код. Там ничего лишнего нет (убрать бы только привязку к payment_id {"21507", "21508", "21532131"}, но это не сложно, наверное).  
 
 
 
#17
22.02.2023 14:45:23
Цитата
написал:
объяснить как убрать привязку к id?
Надо в "шаг" с кода Антона подавать список названий полей (payment_ids).
Если по рабоче-крестьянски, то вот так
Код
let
    a = Table.FromList(Json.Document(Web.Contents("https://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=516275&am...], (x)=>{x}),
    b = Table.ExpandRecordColumn(a, "Column1", {"id", "payments"}),
    b_next = List.Buffer(List.Accumulate(b[payments], {}, (s, c) => s & Record.FieldNames(c))),
    c = Table.ExpandRecordColumn(b, "payments", b_next),
    d = Table.UnpivotOtherColumns(c, {"id"}, "q", "w"),
    q = Table.ExpandRecordColumn(d, "w", {"status", "type", "amount"})
in
    q

Но лучше, если Антон свое решение предложит, потому как "дяденька, я - не сварщик".

Изменено: Alien Sphinx - 22.02.2023 14:45:45
Пришелец-прораб.
 
 
 
#18
22.02.2023 14:57:36
Alien Sphinx, а для чего через List.Accumulate?, если можно проще и быстрее на больших объемах
Код
Table.ExpandRecordColumn(b, "payments", List.Distinct(List.Combine(List.Transform(b[payments], Record.FieldNames))))
 
 
 
#19
22.02.2023 15:02:11
Цитата
написал:
а для чего через List.Accumulate?
Я ж говорю, "я не сварщик". Бывает не вижу самое простое и очевидное решение  :)  
Изменено: Alien Sphinx - 22.02.2023 16:05:50
Пришелец-прораб.
 
 
 
Страницы: 1
Наверх