Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
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
it worked without the "type text" at the end
thanks a lot