I am attempting to parse JSON data in Power BI that has a structure like the following:
{"name":{"0":"Jerry","1":"Ron","2":"Sally","3":"Sue"},"grade":{"0":78,"1":99,"2":88,"3":97}}
Currently, I have tried transforming the data via transform -> To Table -> Parsed JSON but it returns the table in the following format which when I attempt to expand rows shows the table two below. If I expand again, the data is not in the correct format (see table 3, through all the elements in my data). I need the data to be in the format like seen in table 4. Is there a different way to parse data that is in this custom structure?
Table 1:
| Column1 |
| -------- |
| *Record* |
Table 2:
| Name | Score |
| -------- | -------------- |
| *Record* | *Record* |
Table 3:
| Name.0 | Name.1 | Name.2 | Name.3 | Grade.0 | Grade.1 | Grade.2 | Grade.3 |
|---|---|---|---|---|---|---|---|
| Jerry | Ron | Sally | Sue | 78 | 99 | 88 | 87 |
Table 4:
| Name | Score |
|---|---|
| Jerry | 78 |
| Ron | 99 |
| Sally | 88 |
| Sue | 87 |
