Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Pulling data from JSON in Power BI

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.

 

idname
1Shubham
2Swapnil
3Fayaz

 

But i wanted to result to be like this :- 

 

idnameposition
1ShubhamDeveloper
2SwapnilSenior Develop
3Fayaznull

 

Could you please help me with this ? 

Need Help
Show and Tell

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler Please find corrected JSON

       

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

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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