Given the below JSON I'm trying to load it into Excel. The "Ratings" section I would like to format into a single delimited string/cell. I'm pretty new to PowerQuery
so I'm struggling to do this. I've managed to format the list of Records to its own table, but concatenating this into a string and adding it back into my source table is where I'm drawing a blank. Any help would be appreciated.
PowerQuery
let
Source = Json.Document(File.Contents("C:\filename.json")),
Ratings1 = Source[Ratings],
#"Converted to Table" = Table.FromList(Ratings1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
LastStep = Table.ExpandRecordColumn(#"Converted to Table", "Column1", { "Source", "Value" })
in
LastStep
JSON
{
"Title": "Iron Man",
"Year": "2008",
"Rated": "PG-13",
"Ratings": [{
"Source": "Internet Movie Database",
"Value": "7.9/10"
}, {
"Source": "Rotten Tomatoes",
"Value": "93%"
}, {
"Source": "Metacritic",
"Value": "79/100"
}
]
}
Ultimately, something like below would be ideal.