Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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]","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":"[email protected]","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,

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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--descriptionf--datef--location_id
      Paint2017-01-01xxxxxx
      Dogs2017-01-01xxxxxxx

       

      I have 200+ rows.

       

      Thank you, 

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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,