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]","...
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

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,

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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