JSON Data Objects in Objects in an Array

[ck3mp]

Hello,

Im struggling with a JSON data source in PowerBI Desktop. I've managed to get just the headings to output sucesfully using the following: (I've no idea how efficient this is)

let
 Source = Json.Document(File.Contents("C:\Users\Chris.Kemp\Desktop\test.json")),
 #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "created_at", "updated_at", "created_by", "fields"}, {"Column1.id", "Column1.created_at", "Column1.updated_at", "Column1.created_by", "Column1.fields"}),
 #"Column1 fields" = #"Expanded Column1"{0}[Column1.fields],
 #"Converted to Table1" = Table.FromList(#"Column1 fields", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id"}, {"Column1.id"}),
 #"Transposed Table" = Table.Transpose(#"Expanded Column2")
in
 #"Transposed Table"

So I'm left with an empty table with headings:

f--description | f--due_date | f--location_id

I cant get the headings of the first object or any data to populate the table.

I need to have the headings as:

id | created_at | created_by | f--description | f--due_date | f--location_id

And get the two records into the table after the headings. (This is just a data sample showing the structure im working with)

test.json

[{"id":"026f2908-5780-4061-9bbb-521ae81480c7","created_at":"2017-02-22 11:55:15 +0000","updated_at":"2017-02-22 12:09:56 +0000","created_by":"chris.k@b.com","fields":[{"id":"f--description","name":"Description","display_type":"textarea","value":"Paint"},{"id":"f--due_date","name":"Due date","display_type":"date","value":"2017-03-31"},{"id":"f--location_id","name":"Location","display_type":"location","value":"da206e01-91ba-4314-8d2b"}],"comments":[],"attachments":[]},{"id":"0475a275-f5d4-4b27-a47a-3d0caca9a2a7","created_at":"2017-02-22 11:55:55 +0000","updated_at":"2017-02-22 12:09:56 +0000","created_by":"chris.k@b.com","fields":[{"id":"f--description","name":"Description","display_type":"textarea","value":"Dogs"},{"id":"f--due_date","name":"Due date","display_type":"date","value":"2017-03-31"},{"id":"f--location_id","name":"Location","display_type":"location","value":"da206e01-91ba-8d2b-53659bf01891"}],"comments":[],"attachments":[]}]

Thanks in advance for any pointers on this,

Chris

[v-qiuyu-msft]

Hi @ck3mp,

Please modify the Power Query like below:

let
    Source = Json.Document(File.Contents("C:\Users\v-qiuyu\Desktop\test.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "created_at", "updated_at", "created_by", "fields", "comments", "attachments"}, {"Column1.id", "Column1.created_at", "Column1.updated_at", "Column1.created_by", "Column1.fields", "Column1.comments", "Column1.attachments"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.updated_at"}),
    #"Expanded Column1.fields" = Table.ExpandListColumn(#"Removed Columns", "Column1.fields"),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Column1.fields",{"Column1.comments", "Column1.attachments"}),
    #"Expanded Column1.fields1" = Table.ExpandRecordColumn(#"Removed Columns1", "Column1.fields", {"id", "name", "display_type", "value"}, {"Column1.fields.id", "Column1.fields.name", "Column1.fields.display_type", "Column1.fields.value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.fields1",{{"Column1.id", "fields.id"}, {"Column1.fields.name", "fields.name"}, {"Column1.fields.display_type", "fields.display_type"}, {"Column1.fields.value", "fields.value"}, {"Column1.created_at", "created_at"}, {"Column1.created_by", "created_by"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Column1.fields.id]), "Column1.fields.id", "fields.name", List.Count)
in
    #"Pivoted Column"

Best Regards,
Qiuyun Yu