How to insert JSON records into a Common Data Service Entity in PowerApps?

1
votes

I need to insert data entries into Common Data Service(CDS) Entity in PowerApps. This is how my entity looks like:

Entity in CDS

Now if I try to insert the entries by importing an excel table, like shown below, power query correctly interprets it into its own table.

Excel Table

Steps => 'Get Data' > 'Excel' > 'Browse' (onedrive)

Corresponding PowerQuery Interpretation

But if I try to do the same thing via a JSON File, then it isn't able to break it down to the attributes.

Sample JSON:

[
  {
    "patientID": "p1",
    "phoneNumber": 9876543210,
    "patientName": "Abc",
    "DOB": "17/06/2006",
    "Address": "Hyderabad"
  },
  {
    "patientID": "p2",
    "phoneNumber": 9976543210,
    "patientName": "def",
    "DOB": "17/06/2006",
    "Address": "Hyderabad"
  },
  {
    "patientID": "p3",
    "phoneNumber": 9996543210,
    "patientName": "ghi",
    "DOB": "17/06/2006",
    "Address": "Hyderabad"
  }
]

Steps => 'Get Data' > 'JSON' > 'Browse' (onedrive)

Corresponding interpretation of PowerQuery: First

Then if we go into the first record: Second

Now this should be interpreted as a 5-column table, and not a 2 column table with 'Key' and 'Value' as the 2 columns.

A possible solution to this is: 'Convert to Table' > 'Transform Table- Transpose' > 'Transform Table- Use first row as headers'

This gives the following result: Third

But it is still just one record, where it should have been 3 records.

How can I make JSON files being converted into a table the same way as Excel tables are treated? Should the JSON be written in a different way?

1
jsonexcelpowerbipowerquerycommon-data-service
Never post images of code, errors or output! minimal reproducible example – Rob
@Rob ok, I'll keep this in mind for future questions – Sumit Nagpal

1 Answers

2
votes

Once you have your list of records, pass the expression to Table.FromRecords function. It will give you expected output.

To give you an example:

let
    serialised = "[{""patientID"": ""p1"",""phoneNumber"": 9876543210,""patientName"": ""Abc"",""DOB"": ""17/06/2006"",""Address"": ""Hyderabad""},{""patientID"": ""p2"",""phoneNumber"": 9976543210,""patientName"": ""def"",""DOB"": ""17/06/2006"",""Address"": ""Hyderabad""},{""patientID"": ""p3"",""phoneNumber"": 9996543210,""patientName"": ""ghi"",""DOB"": ""17/06/2006"",""Address"": ""Hyderabad""}]",
    json = Json.Document(serialised),
    toTable = Table.FromRecords(json)
in
    toTable

Which gives me:

Output