How to treat an array structure as a table's field on Power Query

[CEGOS14]

Hello, Everyone.

I'm using use the web source on Get Data to submit a JSON request and retrieve data from an app to Power BI. However, in some cases, I retrieve an array structure as one of the table's field, like the JSON Response transcripted below:[
{
"id": "5e34a0a597ccde5ea11630f9",
"email": "xxxxxx@xxxxxxxxxx.xxx.xx",
"name": "xxxxxxxxxxx",
"memberships": [
{
"userId": "5e34a0a597ccde5ea11630f9",
"hourlyRate": {
"amount": 99999,
"currency": "U$"
},
"targetId": "5e35f2eb91b8322612b42342",
"membershipType": "WORKSPACE",
"membershipStatus": "ACTIVE"
},
{
"userId": "5e34a0a597ccde5ea11630f9",
"hourlyRate": null,
"targetId": "5e378e19f4513e6e9ed4c149",
"membershipType": "PROJECT",
"membershipStatus": "ACTIVE"
}
],
"profilePicture": "xxxxxxxxxxxxx",
"activeWorkspace": "5e34a0a697ccde5ea11630fc",
"defaultWorkspace": "5e34a0a697ccde5ea11630fc",
"status": "ACTIVE"
]

How should I treat the "membership" structure transcripted above and marked as red on Power Query? I tried to filter the Response to obtain only the array and treat it as a new table, making the relationship with the original table in another moment, but I don't had success.

Could you help me, please? Thanks in advance.

Regards.

[CEGOS14 · Accepted solution]

First of all, I would like to thank Greg and Maggie for the help. I have followed the Greg's tip and it would work if my data was a normal JSON, but it was a multidimentional JSON. I need to parse it. I have found an article here in Community, but on the Desktop Forum that is exactly my case (https://community.powerbi.com/t5/Desktop/Unable-to-figure-out-how-can-i-make-Multidimension-JSON-Nes...). I saw the YouTube video and it is sound very complex to my Power BI knowledgement, but I will try.

Thanks again for your help.

Regards.

[v-juanli-msft]

Hi @CEGOS14 

After connect to the web source succefully, what result do you get in Power BI(Power query)?

Could you show a screenshot?

Is the Json code above from the web source or something you wanted finally?

Here are some reference how to get json data with Power BI.

https://social.technet.microsoft.com/wiki/contents/articles/37512.create-power-bi-reports-from-json-...

https://community.powerbi.com/t5/Desktop/JSON-data-from-a-web-service-call/td-p/245938

Best Regards

Maggie

[CEGOS14]

Hi, Maggie. Before to send the screenshots, I will try to expand the columns, as suggested by Greg. If the problem persist, I will send some screenshots. Thanks for your help. I will bring news soon.

Regards.

[Greg_Deckler]

That should be a Table that you could then expand.

[CEGOS14]

Hi, Greg. Thanks for your help.

I'll post some Power BI screenshots.

Regards.

[CEGOS14]

Greg, I'll follow your tip and expand the arrays. It is strange to me, because I will have many lines of a primary-key field, but I will try. I will update here soon. Thanks.