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 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

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

     

4 Replies

  • 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