Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Working from JSON data that uses ID as a parent object

Hi All;

 

I'm dealing with data in this format:

 

{  
   "sessions":{  
      "006ccdda-bef3-5124-86a7-f19ea6ba6ed3":{  
         "cost":"$25.00",
         "crdate":"2019-05-19 19:24:08.560685",
         "customer":"Guest",
         "customer_extra":{  

         },
         "customer_id":null,
         "date":"2019-05-19",
         "duration":"30 mins",
         "email":null,
         "end_time":"15:59:03",
         "experience":"VR Experience",
         "games":"Mega Overload / 19 mins ; ABC Paint / 10 mins ; Space Pirate Trainer / 0 mins",
         "pending_payment":"0",
         "start_time":"15:24:08",
         "stations":1,
         "used":true
      },
      "00f071ba-de80-55b8-8a0a-743cb1606b78":{  
         "cost":"$15.90",
         "crdate":"2019-05-20 22:32:20.998229",
         "customer":"Guest",
         "customer_extra":{  

         },
         "customer_id":null,
         "date":"2019-05-20",
         "duration":"30 mins",
         "email":null,
         "end_time":"19:02:12",
         "experience":"VR Experience",
         "games":"Just In Time Incorporated / 11 mins ; Creed: Rise to Glory Arcade / 6 mins ; Nature Treks VR / 4 mins ; The Freefall Dancer / 0 mins",
         "pending_payment":"0.00",
         "start_time":"18:32:20",
         "stations":1,
         "used":true
      },

But when I load it into PowerBI I end up here and don't know how to proceed:

 

So how do I get from this to a table that uses the GUID as an ID column, with each record as the remaining columns in the row?

  • Hi Anonymous 

    From your screenshot, you currently have a record with each field corresponding to a "session".

     

    There should be a Convert => Into Table option on the ribbon:Click Into Table then you will get a table with columns Name and Value.

     

    Click the expand icon on the right of the Value column header to expand the field values to columns

    Then rename columns as required.

     

    Please post back if needed :)

     

    Regards,

    Owen

     

2 Replies

  • Hi Anonymous 

    From your screenshot, you currently have a record with each field corresponding to a "session".

     

    There should be a Convert => Into Table option on the ribbon:Click Into Table then you will get a table with columns Name and Value.

     

    Click the expand icon on the right of the Value column header to expand the field values to columns

    Then rename columns as required.

     

    Please post back if needed :)

     

    Regards,

    Owen

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks!