Forum Discussion
Shape JSON Data from web API
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":"[email protected]","...
Hi Anonymous,
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
Hi Anonymous,
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
Thank you for this Anonymous,
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,
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 Anonymous solution for the headings and append the tables.
Thanks,
Hi Anonymous,
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
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":"[email protected]","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":"[email protected]","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 [email protected] 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 [email protected] 777 333 31-Mar-17 LC-00096 ??? 555 Void No Derek Briggs Greg O'Mahony Greg O'Mahony