Sum value inside records in a List

[juliano_nunes]

I have a table that one of its columns has a list of records on each row, like this:

ID(GUID) | Name(Varchar) | Addresses(List of records)

The number of records inside each address list can vary from 0 to N and the record has the following fields (Street, Number, City, State, Rent Amount). 

If I try to expand this Addresses column I have the options Expand to new lines and Expand values, however I don't want to have multiple lines because of having multiple addresses and the Expand values causes the error saying that can't convert a value of type Record to a type Text.

How can I transform this Addresses column or create a new one to just sum the Rent Amount so the table becomes like the example below?

ID(GUID) | Name(Varchar) | Sum Of Rent Amounts

[
  {
    "id": "5f92e82027694b523ef3e2b2",
    "name": "Becky Reyes",
    "addresses": [
      {
        "street": "High Street",
        "number": 214,
        "city": "Clay",
        "state": "Virgin Islands",
        "rentAmount": "152.1783"
      },
      {
        "street": "Lake Place",
        "number": 908,
        "city": "Advance",
        "state": "Arizona",
        "rentAmount": "158.3097"
      },
      {
        "street": "Vernon Avenue",
        "number": 931,
        "city": "Elrama",
        "state": "Illinois",
        "rentAmount": "146.6831"
      },
      {
        "street": "Dewitt Avenue",
        "number": 236,
        "city": "Fairhaven",
        "state": "Louisiana",
        "rentAmount": "141.0833"
      }
    ]
  },
  {
    "id": "5f92e8202d22a6e0e2bed2c3",
    "name": "Young Mcconnell",
    "addresses": [
      {
        "street": "Locust Avenue",
        "number": 669,
        "city": "Hollins",
        "state": "Georgia",
        "rentAmount": "133.0197"
      },
      {
        "street": "Canda Avenue",
        "number": 847,
        "city": "Choctaw",
        "state": "Wisconsin",
        "rentAmount": "96.3367"
      },
      {
        "street": "Aurelia Court",
        "number": 148,
        "city": "Newcastle",
        "state": "Ohio",
        "rentAmount": "129.8063"
      },
      {
        "street": "Vista Place",
        "number": 173,
        "city": "Salunga",
        "state": "Kentucky",
        "rentAmount": "159.3151"
      }
    ]
  },
  {
    "id": "5f92e820d3a06269257b7591",
    "name": "Alvarado Woodard",
    "addresses": [
      {
        "street": "Gunther Place",
        "number": 530,
        "city": "Gibsonia",
        "state": "New Mexico",
        "rentAmount": "100.6517"
      },
      {
        "street": "Hunterfly Place",
        "number": 378,
        "city": "Morningside",
        "state": "Northern Mariana Islands",
        "rentAmount": "100.8699"
      }
    ]
  },
  {
    "id": "5f92e820ab4778ee175654ce",
    "name": "Nicole Boone",
    "addresses": [
      {
        "street": "Noll Street",
        "number": 397,
        "city": "Otranto",
        "state": "Nevada",
        "rentAmount": "50.1722"
      },
      {
        "street": "Arkansas Drive",
        "number": 630,
        "city": "Russellville",
        "state": "Alaska",
        "rentAmount": "67.1975"
      },
      {
        "street": "School Lane",
        "number": 892,
        "city": "Datil",
        "state": "Alabama",
        "rentAmount": "93.9239"
      },
      {
        "street": "Adelphi Street",
        "number": 694,
        "city": "Topanga",
        "state": "Wyoming",
        "rentAmount": "75.4199"
      }
    ]
  },
  {
    "id": "5f92e8200bf2afef3da5630f",
    "name": "Rodgers Moore",
    "addresses": [
      {
        "street": "Seacoast Terrace",
        "number": 964,
        "city": "Buxton",
        "state": "Delaware",
        "rentAmount": "157.2112"
      },
      {
        "street": "Bush Street",
        "number": 576,
        "city": "Aberdeen",
        "state": "District Of Columbia",
        "rentAmount": "142.3866"
      },
      {
        "street": "Foster Avenue",
        "number": 788,
        "city": "Tooleville",
        "state": "Florida",
        "rentAmount": "159.5113"
      }
    ]
  }
]

[amitchandak · Accepted solution]

@juliano_nunes , You should expand the list in a row if possible.

refer if this can help: https://radacad.com/convert-flat-list-to-table-in-power-bi-pivot-without-a-set-key-column-using-powe...

[Anonymous]

Hi @juliano_nunes ,

Please applied the below steps in Power Query Editor to achieve it, you can find the details in my sample pbix file(see attachment):

let
    Source = Json.Document(File.Contents("D:\Test.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "addresses"}, {"id", "name", "addresses"}),
    #"Expanded addresses" = Table.ExpandListColumn(#"Expanded Column1", "addresses"),
    #"Expanded addresses1" = Table.ExpandRecordColumn(#"Expanded addresses", "addresses", {"street", "number", "city", "state", "rentAmount"}, {"addresses.street", "addresses.number", "addresses.city", "addresses.state", "addresses.rentAmount"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded addresses1",{{"addresses.rentAmount", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id", "name"}, {{"Sum of Rent Amount", each List.Sum([addresses.rentAmount]), type nullable number}})
in
    #"Grouped Rows"

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[FrankAT]

Hi @juliano_nunes 

save your data as json file and load it in power bi. You'll get the following result:

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[jairoaol · Accepted solution]

uploads sample data.

I can think of you expanding into lines and then grouping values to get the summation you're looking for.

[amitchandak · Accepted solution]

@juliano_nunes , You should expand the list in a row if possible.

refer if this can help: https://radacad.com/convert-flat-list-to-table-in-power-bi-pivot-without-a-set-key-column-using-powe...

[juliano_nunes]

Expanding the column into multiple rows and grouping them right after does work, however in my real life scenario I have other 25 columns that I'd need to add to the group. That's why I was looking for a more "automated" solution, but if it doesn't exist, it's fine.