Parse Json and Model data

[Anonymous]

I have a sql table that contains Id, UserId and Data columns. The data column has value in json format.

Id column is 123, UserId column is rew1325Eb and the data column is

The json structure is as follows:

{

    "policydate": "2021-10-26",

    "candidates": [

        {

            "Fullname": "Luke Salvy",

            "income": {

                "basicSalary": 30000,

                "overtime": 100

            },

        {

            "Fullname": "Peter Rams",

            "income": {

                "basicSalary": 35000,

                "overtime": 200

        } 

    ]

}

The issues I'm facing is to have Candidates split to Candidate1, Candidate2 and so on if more candidate appears in the json in power BI.

for example:
Candidate1
Fullname = Luke Salvy
basicSalary = 30000
overtime = 100

Candidate2
Fullname = Peter Rams
basicSalary = 35000
overtime = 200

This is for forward calculation.
How can I achieve this in Power BI

[amitchandak]

@Anonymous , You JSON has some problem , it should be like this

{

    "policydate": "2021-10-26",

    "candidates": [

        {

            "Fullname": "Luke Salvy",

            "income": {

                "basicSalary": 30000,

                "overtime": 100

		}},

        {

            "Fullname": "Peter Rams",

            "income": {

                "basicSalary": 35000,

                "overtime": 200

        } } 

    ]

}

Power BI is generating write code for this

let
    Source = Json.Document(File.Contents("C:\Users\Amit.Chandak\Downloads\test.json")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded candidates" = Table.ExpandListColumn(#"Converted to Table", "candidates"),
    #"Expanded candidates1" = Table.ExpandRecordColumn(#"Expanded candidates", "candidates", {"Fullname", "income"}, {"candidates.Fullname", "candidates.income"}),
    #"Expanded candidates.income" = Table.ExpandRecordColumn(#"Expanded candidates1", "candidates.income", {"basicSalary", "overtime"}, {"candidates.income.basicSalary", "candidates.income.overtime"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded candidates.income",{{"policydate", type date}, {"candidates.Fullname", type text}, {"candidates.income.basicSalary", Int64.Type}, {"candidates.income.overtime", Int64.Type}})
in
    #"Changed Type"

[Anonymous]

Yes, it generate the M query from the JSON as shown

But how to split candidates with their respective income. For example , Luke Salvy is the Main applicant while Peter Rams is Additional applicant. I want to split them so I can calculate their individual tax Band later