Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
@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"
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
82 | |
65 | |
64 | |
56 |
User | Count |
---|---|
171 | |
111 | |
109 | |
72 | |
71 |