Shape JSON Data from web API

[chris_k]

I am struggling to extract the data from my JSON source (see below)

[{"id":"TEST1","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--date","name":"Date","display_type":"date","value":"2017-03-31"},{"id":"f--location_id","name":"Location","display_type":"location","value":"da206e01"}],"comments":[],"attachments":[]},{"id":"TEST2","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--date","name":"Date","display_type":"date","value":"2017-03-31"},{"id":"f--location_id","name":"Location","display_type":"location","value":"da206e01"}],"comments":[],"attachments":[]}]

My goal is to have the headings:

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

I have gotten as far as extracting just the last three headings using:

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"

However I cannot get the data or the inital three headings.

Thanks for any help,

[v-shex-msft]

Hi @chris_k,

You can try to use Records.FieldName and Table.FromList function to achieve your requirement:

Full query:

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],
    Table=Table.FromList(#"Column1 fields",Record.FieldValues,Record.FieldNames(#"Expanded Column1"{0}[Column1.fields]{0}))
in
    Table

Regards,

Xiaoxin Sheng

[chris_k]

Thank you for this @v-shex-msft,

Just building upon this. When I use this query I get one record with the correct headers (I then use remove column and transpose to get my data how I need it) but it only works for one record...

Can I itterate into records to make more rows?

So I end up with:

f--description	f--date	f--location_id
Paint	2017-01-01	xxxxxx
Dogs	2017-01-01	xxxxxxx

I have 200+ rows.

Thank you, 

[chris_k]

I have got a bit closer using:

let
    Source = Json.Document(File.Contents("C:\Users\Chris.Kemp\Desktop\Secondment\PowerBI Trial\Field\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 fields1" = #"Expanded Column1"[Column1.fields],
    #"TableFromRows" = Table.FromRows(#"Column1 fields1"),
    #"Expanded Column3" = Table.ExpandRecordColumn(#"TableFromRows", "Column3", {"value"}, {"Column3.value"}),
    #"Expanded Column4" = Table.ExpandRecordColumn(#"Expanded Column3", "Column2", {"value"}, {"Column2.value"}),
    #"Expanded Column5" = Table.ExpandRecordColumn(#"Expanded Column4", "Column1", {"value"}, {"Column1.value"})
in
    #"Expanded Column5"

This would be fine but my actual data contains 42columns that can alter on occasion. I cant seem to find a way to expand all.

My plan was to do this then use @v-shex-msft solution for the headings and append the tables.

Thanks,

[v-shex-msft]

Hi @chris_k,

You can share the more fully sample data and the expected result, I will try to modify the formula if it can works on your scenario.

Regards,

Xiaoxin Sheng

[chris_k]

Thank you,

The input data is (2 rows included to save space) actual data is 4000 rows.

Input:

[{"id":"026f2908","created_at":"2017-02-22 11:55:15 +0000","updated_at":"2017-02-22 12:09:56 +0000","created_by":"chris.kemp@b.com","fields":[{"id":"f--description","name":"Description","display_type":"textarea","value":"Paint next to sinks coming off"},{"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-53659bf01891"},{"id":"f--root_cause_id","name":"Root cause","display_type":"root_cause_id","value":null},{"id":"f--issue_type_id","name":"Issue type","display_type":"issue_type_id","value":"dad92afc-b3ef-4a40-98b3-9d5cbd2c48aa"},{"id":"f--full_description","name":"Full description","display_type":"text","value":null},{"id":"f--identifier","name":"Identifier","display_type":"text","value":"CK-00072"},{"id":"f--status","name":"Status","display_type":"drop-down","value":"Void"},{"id":"f--responsible_company_id","name":"Responsible company","display_type":"company_id","value":"9476e610-7093-4b10-bef6-3a4e9a66622e"},{"id":"cf--09640a4d-8e1e-4ef2-b0c2-c0cabb03f0d8","name":"001 - Defects Title","display_type":"text","value":null},{"id":"cf--2a84eb10-316e-4f20-a0ef-5fa6a345ac13","name":"002 - Date Logged With BB","display_type":"date","value":"01/12/2017"},{"id":"cf--9feb7623-40a2-4e98-8c01-e049a8b17687","name":"003 - Health & Safety Issue","display_type":"drop-down","value":"No"},{"id":"cf--99b7470a-1e10-47c4-b57d-9e11c3bac972","name":"004 - Location Details","display_type":"text","value":"Female dry side changing rooms"},{"id":"cf--1bb8abec-f0b7-48e6-83be-2993b1799605","name":"005 - Client Ref","display_type":"text","value":null},{"id":"cf--474114c7-d4b4-4312-9e9c-8496079d827e","name":"006 - Date Acknowledged","display_type":"date","value":""},{"id":"cf--621d146a-047f-460c-b1fb-ef6d64e8bc37","name":"007 - Date Refuted","display_type":"date","value":""},{"id":"cf--f7e2b3c6-5b8d-4178-bf8c-0279a072b9d9","name":"008 - Priority (Urgency)","display_type":"drop-down","value":null},{"id":"cf--7b4a339d-aff9-4b00-8ae5-e90a605c8d8d","name":"009 - OPS Director","display_type":"drop-down","value":null},{"id":"cf--ffb876e9-4798-4615-b8bc-39de15e27b6c","name":"010 - BB Project Manager","display_type":"drop-down","value":null},{"id":"cf--c73d5a76-ddd4-404b-86e8-500fab1726bc","name":"011 - BB A/C Manager","display_type":"drop-down","value":null},{"id":"cf--9ab9d3d2-f86e-490f-a4bd-df36582707cf","name":"012 - BB Issue Owner","display_type":"drop-down","value":null},{"id":"cf--54683e87-0bf3-417d-b706-38f3835bb39e","name":"013 - Potential Cost of Repair to BB","display_type":"drop-down","value":null},{"id":"cf--ea5fd732-e397-4cb6-ac15-03206f4d3e3d","name":"014 - Accepted as Defect","display_type":"drop-down","value":null},{"id":"cf--5e6d0fb6-3967-4eaf-89ba-378e515b0334","name":"015 - Responsible Supplier / SC","display_type":"drop-down","value":null},{"id":"cf--60de109a-3cbb-49b3-80ba-af9918c12b7b","name":"016 - Third Party Supplier / SC","display_type":"drop-down","value":null},{"id":"cf--9016b4b5-1565-49e7-a0c7-0b9370ac7ce4","name":"017 - R12 S/C Order Number","display_type":"text","value":null},{"id":"cf--805a59bd-bf71-4f17-aac1-9d19d60c2cb3","name":"018 - S/C Task Code","display_type":"text","value":null},{"id":"cf--092cb819-9ded-4872-88ea-f1866ba452b9","name":"019 -  System / Sub System","display_type":"drop-down","value":null},{"id":"cf--a2033e25-8df0-4a97-a545-db46ea8cdb41","name":"020 - Initial Action Required","display_type":"text","value":null},{"id":"cf--7f9dde29-bbd4-4127-bc3d-d4f0d0ca1800","name":"021 - Target Date for Works","display_type":"text","value":null},{"id":"cf--47f4e081-3d93-4701-a6d5-9557d44795ca","name":"022 - Actual Date for Works","display_type":"date","value":""},{"id":"cf--a7bd3776-03f1-472e-82e6-d49972028893","name":"023 - Date Authorisation to Spend Approved","display_type":"date","value":""},{"id":"cf--92c206b8-ac62-4d43-8b91-780fa1d1345b","name":"024 - Restrictions on Works","display_type":"text","value":"Access limited due to operating Leisure centre. May only be able to access out of hours (Nights) or with agreed special permission from building user"},{"id":"cf--dcde7509-d198-42a4-8be6-18b59cac924e","name":"025 - Scope of Fix","display_type":"text","value":null},{"id":"cf--29911dc8-f289-4d64-a0f5-6fc0a49f5178","name":"026 - RAMS Approved","display_type":"drop-down","value":null},{"id":"cf--a2bdbda4-50b4-4c77-a421-fe9bc4d2f610","name":"027 - Actions to Prevent Reoccurrence","display_type":"text","value":null},{"id":"cf--8d60f62c-db02-42d8-89a8-c3d28b0d1010","name":"028 - Discipline","display_type":"drop-down","value":null},{"id":"cf--6e2960de-b8ef-45b0-8a97-fa815f06200c","name":"029 - Updates","display_type":"text","value":null}],"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.kemp@b.com","fields":[{"id":"f--description","name":"Description","display_type":"textarea","value":"Fire Door Keep Closed' sign is missing"},{"id":"f--due_date","name":"Due date","display_type":"date","value":"2017-01-20"},{"id":"f--location_id","name":"Location","display_type":"location","value":"da206e01-91ba-4314-8d2b-53659bf01891"},{"id":"f--root_cause_id","name":"Root cause","display_type":"root_cause_id","value":null},{"id":"f--issue_type_id","name":"Issue type","display_type":"issue_type_id","value":"dad92afc-b3ef-4a40-98b3-9d5cbd2c48aa"},{"id":"f--full_description","name":"Full description","display_type":"text","value":null},{"id":"f--identifier","name":"Identifier","display_type":"text","value":"CK-00111"},{"id":"f--status","name":"Status","display_type":"drop-down","value":"Void"},{"id":"f--responsible_company_id","name":"Responsible company","display_type":"company_id","value":"9476e610-7093-4b10-bef6-3a4e9a66622e"},{"id":"cf--09640a4d-8e1e-4ef2-b0c2-c0cabb03f0d8","name":"001 - Defects Title","display_type":"text","value":null},{"id":"cf--2a84eb10-316e-4f20-a0ef-5fa6a345ac13","name":"002 - Date Logged With BB","display_type":"date","value":"12/14/2016"},{"id":"cf--9feb7623-40a2-4e98-8c01-e049a8b17687","name":"003 - Health & Safety Issue","display_type":"drop-down","value":"No"},{"id":"cf--99b7470a-1e10-47c4-b57d-9e11c3bac972","name":"004 - Location Details","display_type":"text","value":"First aid room"},{"id":"cf--1bb8abec-f0b7-48e6-83be-2993b1799605","name":"005 - Client Ref","display_type":"text","value":null},{"id":"cf--474114c7-d4b4-4312-9e9c-8496079d827e","name":"006 - Date Acknowledged","display_type":"date","value":""},{"id":"cf--621d146a-047f-460c-b1fb-ef6d64e8bc37","name":"007 - Date Refuted","display_type":"date","value":""},{"id":"cf--f7e2b3c6-5b8d-4178-bf8c-0279a072b9d9","name":"008 - Priority (Urgency)","display_type":"drop-down","value":null},{"id":"cf--7b4a339d-aff9-4b00-8ae5-e90a605c8d8d","name":"009 - OPS Director","display_type":"drop-down","value":null},{"id":"cf--ffb876e9-4798-4615-b8bc-39de15e27b6c","name":"010 - BB Project Manager","display_type":"drop-down","value":null},{"id":"cf--c73d5a76-ddd4-404b-86e8-500fab1726bc","name":"011 - BB A/C Manager","display_type":"drop-down","value":null},{"id":"cf--9ab9d3d2-f86e-490f-a4bd-df36582707cf","name":"012 - BB Issue Owner","display_type":"drop-down","value":null},{"id":"cf--54683e87-0bf3-417d-b706-38f3835bb39e","name":"013 - Potential Cost of Repair to BB","display_type":"drop-down","value":null},{"id":"cf--ea5fd732-e397-4cb6-ac15-03206f4d3e3d","name":"014 - Accepted as Defect","display_type":"drop-down","value":null},{"id":"cf--5e6d0fb6-3967-4eaf-89ba-378e515b0334","name":"015 - Responsible Supplier / SC","display_type":"drop-down","value":null},{"id":"cf--60de109a-3cbb-49b3-80ba-af9918c12b7b","name":"016 - Third Party Supplier / SC","display_type":"drop-down","value":null},{"id":"cf--9016b4b5-1565-49e7-a0c7-0b9370ac7ce4","name":"017 - R12 S/C Order Number","display_type":"text","value":null},{"id":"cf--805a59bd-bf71-4f17-aac1-9d19d60c2cb3","name":"018 - S/C Task Code","display_type":"text","value":null},{"id":"cf--092cb819-9ded-4872-88ea-f1866ba452b9","name":"019 -  System / Sub System","display_type":"drop-down","value":null},{"id":"cf--a2033e25-8df0-4a97-a545-db46ea8cdb41","name":"020 - Initial Action Required","display_type":"text","value":null},{"id":"cf--7f9dde29-bbd4-4127-bc3d-d4f0d0ca1800","name":"021 - Target Date for Works","display_type":"text","value":null},{"id":"cf--47f4e081-3d93-4701-a6d5-9557d44795ca","name":"022 - Actual Date for Works","display_type":"date","value":""},{"id":"cf--a7bd3776-03f1-472e-82e6-d49972028893","name":"023 - Date Authorisation to Spend Approved","display_type":"date","value":""},{"id":"cf--92c206b8-ac62-4d43-8b91-780fa1d1345b","name":"024 - Restrictions on Works","display_type":"text","value":"Access limited due to operating Leisure centre. May only be able to access out of hours (Nights) or with agreed special permission from building user"},{"id":"cf--dcde7509-d198-42a4-8be6-18b59cac924e","name":"025 - Scope of Fix","display_type":"text","value":null},{"id":"cf--29911dc8-f289-4d64-a0f5-6fc0a49f5178","name":"026 - RAMS Approved","display_type":"drop-down","value":null},{"id":"cf--a2bdbda4-50b4-4c77-a421-fe9bc4d2f610","name":"027 - Actions to Prevent Reoccurrence","display_type":"text","value":null},{"id":"cf--8d60f62c-db02-42d8-89a8-c3d28b0d1010","name":"028 - Discipline","display_type":"drop-down","value":null},{"id":"cf--6e2960de-b8ef-45b0-8a97-fa815f06200c","name":"029 - Updates","display_type":"text","value":null}],"comments":[],"attachments":[]}]

Output: 

(I can not figure how to attach things?)

id

created_at

updated_at

created_by

Issue type

Location

Root cause

Due date

Identifier

Full description

Description

Responsible company

Status

001 - Defects Title

002 - Date Logged With BB

003 - Health & Safety Issue

004 - Location Details

005 - Client Ref

006 - Date Acknowledged

007 - Date Refuted

008 - Priority (Urgency)

009 - OPS Director

010 - BB Project Manager

011 - BB A/C Manager

012 - BB Issue Owner

013 - Potential Cost of Repair to BB

014 - Accepted as Defect

015 - Responsible Supplier / SC

016 - Third Party Supplier / SC

017 - R12 S/C Order Number

018 - S/C Task Code

019 - System / Sub System

020 - Initial Action Required

021 - Target Date for Works

022 - Actual Date for Works

023 - Date Authorisation to Spend Approved

024 - Restrictions on Works

025 - Scope of Fix

026 - RAMS Approved

027 - Actions to Prevent Reoccurrence

028 - Discipline

029 - Updates

333

2017-02-22 11:55:15

2017-02-22 11:55:15

chris.kemp@b.com

777

666

31-Mar-17

LC-00112

???

555

Void

No

James

Greg O'Mahony

Greg O'Mahony

4444

2017-02-22 11:55:15

2017-02-22 11:55:15

chris.kemp@b.com

777

333

31-Mar-17

LC-00096

???

555

Void

No

Derek Briggs

Greg O'Mahony

Greg O'Mahony

[v-shex-msft · Accepted solution]

Hi @chris_k,

You can refer to below sample with used to analysis your data. (I split these records to two parts and use index to merge them)

Regards,

Xiaoxin Sheng