Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Table.ExpandListColumn - list splitting and avoiding rows duplicating

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...
Need Help
  • Stachu's avatar
    Stachu
    6 years ago

    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

     

Stachu's avatar
Stachu
Community Champion
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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

RatioannualizedeffectiveDatedeclarationDatetypegap
$.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's avatar
    Stachu
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      it worked without the "type text" at the end

       

      thanks a lot