Forum Discussion
Anonymous
4 years agoNot applicable
Parse Json and Model data
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 ...
amitchandak
Super User
4 years agoAnonymous , 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
4 years agoNot applicable
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