Forum Discussion
How to treat an array structure as a table's field on Power Query
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": "[email protected]",
"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.
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-Nested/td-p/324041). 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.
6 Replies
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-Nested/td-p/324041). 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.
- Greg_Deckler
Community Champion
That should be a Table that you could then expand. 
v-juanli-msftCommunity Support
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://community.powerbi.com/t5/Desktop/JSON-data-from-a-web-service-call/td-p/245938
Best Regards
Maggie
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.
