Table.ExpandListColumn - list splitting and avoiding rows duplicating

[Anonymous]

Hi all,

I am parsing a JSON with nested records and lists. Now I'm using the Advanced Editor and I'm trying a way to split my list and take just the field of the first occurrency (it's dating sorted and it's fine for me taking the first one).

The problem is that i'm using the Table.ExpandListColumn function that splits the list into a row for each item and values in the other columns are duplicated in each new row created.

Is there any other way to do it?

thanks

Antonio

[Stachu]

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

also the JSON example would be very helpful

[Anonymous]

Thanks for answering me

here a sample json response I get from a rest service that I call with customer name

{
"data": {
"ratio": "5.89%",
"annualized": "0.88",
"payments": {
"rows": [
{
"effectiveDate": "02/06/2020",
"type": "cash",
"amount": "$0.22",
"declarationDate": "01/28/2020",
"recordDate": "02/07/2020",
"paymentDate": "02/13/2020"
},
{
"effectiveDate": "11/01/2019",
"type": "check",
"amount": "$0.22",
"declarationDate": "10/23/2019",
"recordDate": "11/04/2019",
"paymentDate": "11/14/2019"
},
{
"effectiveDate": "08/02/2019",
"type": "check",
"amount": "$0.22",
"declarationDate": "07/24/2019",
"recordDate": "08/05/2019",
"paymentDate": "08/15/2019"
}
]
}
},
"message": null,
"status": {
"rCode": 200,
"bCodeMessage": null,
"developerMessage": null
}
}

for each customer i have different answer and for each answer I woul like to insert just one row as following

and here the table that I would like to realize

Ratio	annualized	effectiveDate	declarationDate	type	gap
$.data.ratio	$.data.annualized	data.payments.rows[0].effectiveDate	data.payments.rows[0].declarationDate	data.payments.rows[0].type	effectiveDate-declarationDate

Since Expanding list mean duplicating at least three columns means having a very heavy table

thanks for your help

[Stachu · Accepted solution]

try adapting this code, it takes first record from the list of records:

let
    Source = Json.Document(File.Contents("C:\tests\test.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"data", type any}, {"message", type any}, {"status", type any}}),
    #"Expanded data" = Table.ExpandRecordColumn(#"Changed Type", "data", {"ratio", "annualized", "payments"}, {"ratio", "annualized", "payments"}),
    #"Expanded payments" = Table.ExpandRecordColumn(#"Expanded data", "payments", {"rows"}, {"rows"}),
    transform = Table.TransformColumns(#"Expanded payments", {{"rows", each _{0}, type text}})
in
    transform

[Anonymous]

it worked without the "type text" at the end

thanks a lot