Data Modeling Question with an API

[EnzTDI91]

Hello,

I am running into an issue where an API endpoint structure is just not making sense to me at all. I am working with a client, they have an export from this website we use and it pulls down all the project data in the correct format. The client would like this to be automated, so my plan was to use PowerBI to grab the API endpoints and model the data in there and then just move the queries to excel which would then be able to refresh and have all the steps needed to model the data correctly. 
Here is the problem I am running into.

When I grab the projects endpoint and include custom fields parameter in the https string it adds a column that can be expanded to the table: see below.

When I expand the column on the left it gives me the custom field ID and the name of the Custom field but not the value./

When I expand the included.CustomFieldsProjects column it will give me custom field ID and the value but it is giving me a bunch of options to expand and Im not even sure why(Im assuming these are projectIDs but they are not any of the project IDs that we are looking for:

Can someone help me? I have no clue how to model this like below with the custom fields as columns per project and the values are in the rows:

[v-priyankata]

Hi @EnzTDI91 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[v-priyankata]

Hi @EnzTDI91 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted  If yes, marking the relevant solution would be awesome for others who might run into the same thing.

[v-priyankata]

Hi @EnzTDI91 
Thank you for reaching out to the Microsoft Fabric Community.

The issue seems to stem from the nested JSON data where included.customfields provides field IDs and names and included.customFieldsProjects links these to project IDs with values, but the relationships aren’t aligning due to multiple expandable options. Here’s how you can address this:

• Expand included.customfields for field IDs and names, then included.customFieldsProjects for project IDs and values; merge using customfieldId to align names with values.
• In Power Query, pivot the table with custom field ID as the "Values Column," custom field name as "Rows to Pivot," and "Don’t Aggregate" for raw values. 
• Filter or group by project ID and custom field ID to ensure unique entries before pivoting.
• Check the transformed data in Power BI, then export queries to Excel for automated refresh with proper API parameters.

If this information is helpful, please “Accept as solution” to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-priyankata]

Hi @EnzTDI91 

I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.

[lbendlin]

is the API returning JSON or XML?  Have you looked at that data in a JSON or XML parser?