I got a CSV file which contains a nested JSON - when reading with excel power query the whole JSON code is put into one cell. How do I display the contained information correctly in excel? How to properly seperate Objects {} and Arrays []?
Goal would be to have columns with the headings: time, autonomy, voltage, speed etc... -since the time of measurement is the same for all of them
code snippet from the start, containing autonomy data of an electric car:
_id,events,id,itinerary.geometry,itinerary.id,itinerary.properties,itinerary.type,segments,timeSeries
6D92E6FA633F4A4F2783951C0DA7AB74,[],6D92E6FA633F4A4F2783951C0DA7AB74,,6D92E6FA633F4A4F2783951C0DA7AB74,,Feature,[],"
[{""name"":""autonomy"",""unit"":""%"",""data"":[
{""date"":""2021-09-25T11:40:33.000Z"",""value"":3.29},
{""date"":""2021-09-25T11:40:48.000Z"",""value"":3.29},
{""date"":""2021-09-25T11:41:03.000Z"",""value"":3.29},
{""date"":""2021-09-25T11:41:18.000Z"",""value"":3.29},
.....and so on
after a while comes a new array containing voltage data []
]},{""name"":""auxBattery"",""unit"":""volt"",""data"":[
{""date"":""2021-09-25T11:40:33.000Z"",""value"":10.33},
{""date"":""2021-09-25T11:40:48.000Z"",""value"":10.36},
{""date"":""2021-09-25T11:41:03.000Z"",""value"":10.38},
{""date"":""2021-09-25T11:41:18.000Z"",""value"":10.37},
....and so on
and another containing speed data:
{""name"":""speed"",""unit"":""Kph"",""data"":[
{""date"":""2021-09-25T11:40:33.000Z"",""value"":0},
{""date"":""2021-09-25T11:40:48.000Z"",""value"":0},
{""date"":""2021-09-25T11:41:03.000Z"",""value"":0},
......