Parsing JSON in Power BI with a Custom Structure-CodePudding

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

CodePudding user response：

let
    Source = Json.Document("{""name"":{""0"":""Jerry"",""1"":""Ron"",""2"":""Sally"",""3"":""Sue""},""grade"":{""0"":78,""1"":99,""2"":88,""3"":97}}"),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"0", "1", "2", "3"}, {"0", "1", "2", "3"}),
    #"Transposed Table" = Table.Transpose(#"Expanded Value"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"name", type text}, {"grade", Int64.Type}})
in
    #"Changed Type"

Results in