Pulling data from JSON in Power BI

[Anonymous]

Hi Team,

I am pulling data from MongoDB in Power BI and data is in document format.

(

id:- 1

name :- Shubham

Position :- Developer

}

(

id:- 2

name :- Swapnil

Position :- Senior Developer

}

(

id:- 3

name :- Fayaz

}

Now this is supposed to be 3 rows and 3 columns in Power BI, but Position column is not avaialble in 3rd JSON due to which it only id and name by excluding Position column from entire dataset.

id	name
1	Shubham
2	Swapnil
3	Fayaz

But i wanted to result to be like this :- 

id	name	position
1	Shubham	Developer
2	Swapnil	Senior Develop
3	Fayaz	null

Could you please help me with this ? 

[v-lili6-msft]

hi  @Anonymous 

I have test on my side, it works well.

and here is power query code:

let
    Source = Json.Document(File.Contents("C:\Users\linli\Desktop\test.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "Position"}, {"id", "name", "Position"})
in
    #"Expanded Column1"

Regards,

Lin

[Greg_Deckler]

I cannot get that JSON to validate so hard to help.

https://jsonlint.com/

[Anonymous]

@Greg_Deckler Please find corrected JSON

[{
"id": 1,
"name": "Shubham",
"Position": "Developer"
},
{
"id": 2,
"name": "Swapnil",
"Position": "Senior Developer"
},
{
"id": 3,
"name": "Fayaz"
}
]