json to excel conversion

[kwame84]

I have the sample file below that I need to convert to excel with specific headers. I need to convert the json data to the following excel table format:


},
"relationshipExternalIdentifier": {
"example": "5003000000D8cuI",
"type": "string"
},
"name": {
"example": "Sample Opportunity",
"type": "string"
},
"lastModifiedBy": {
"example": "user@bank.com",
"type": "string"
},
"closeDate": {
"format": "date-time",
"type": "string"
},
"firstMonthRegulatoryRiskWeightedAssets": {
"format": "double",
"type": "number"

Field Name	Type	Format	Example
relationshipExternalIdentifier	string		5003000000D8cuI
name	string		Sample Opportunity
lastModifiedBy	string		user@bank.com
closeDate	string	date-time

 

[alansidman]

Can be done with Power Query

 

[kwame84]

I've been playing around with power query and I'm running into a complication where my json file has parent and child values . How do I extract everything into one excel. Power query will only show me the parent with the child as a record link. when I click that i lose everything from the parent. not sure if this makes sense.

For example, the "financialStatement" (row 18) has a child record in column "Value.properties." How do I extract that record and all records into one excel sheet without having to click each child record and manually extract one at a time?

Here's the sample json file with the parent/child sections.


{
"items": {
"type": "object",
"properties": {
"clientId": {
"format": "uuid",
"example": "b92cd14b-692b-4acc-95eb-40605a499dab",
"type": "string"
},
"relationshipExternalIdentifier": {
"example": "5003000000D8cuI",
"type": "string"
},
"name": {
"example": "Sample Opportunity",
"type": "string"
},
"lastModifiedBy": {
"example": "user@bank.com",
"type": "string"
},
"closeDate": {
"format": "date-time",
"type": "string"
},
"prospectId": {
"format": "uuid",
"example": "d6d254ca-3779-4135-8283-362dd4b98907",
"type": "string"
},
"relationshipId": {
"format": "uuid",
"example": "b92cd14b-692b-4acc-95eb-40605a499dab",
"type": "string"
},
"externalSystemId": {
"example": "4207000000D8cuI",
"type": "string"
},
"id": {
"format": "uuid",
"example": "b92cd14b-692b-4acc-95eb-40605a499dab",
"type": "string"
},
"prospectName": {
"example": "Sample Prospect",
"type": "string"
},
"created": {
"format": "date-time",
"type": "string"
},
"lastModified": {
"format": "date-time",
"type": "string"
},
"relationship": {
"example": "Food Truck",
"type": "string"
},
"probability": {
"format": "double",
"example": 0.1,
"type": "number"
},
"pricingDate": {
"format": "date-time",
"type": "string"
},
"stage": {
"example": "Quoted",
"type": "string"
},
"creator": {
"example": "user@bank.com",
"type": "string"
},
"financialStatement": {
"type": "object",
"properties": {
"otherAppliedEarningsCredit": {
"format": "double",
"type": "number"
},
"interestExpense": {
"format": "double",
"type": "number"
},
"taxes": {
"format": "double",
"type": "number"
},
"lastCalculated": {
"format": "date-time",
"type": "string"
},
"otherAccountRevenueEarningsCreditGenerated": {
"format": "double",
"type": "number"