API Data transform

[vwiles84]

I have connected to an API in Power BI desktop. When transforming the data, I can covert into a table, and when I do I get a short list with a column of Name and Value.

I expand the values to rows and get the following:

My question is, how do I transform these rows and column to get the actual values in the dataset? 

[v-eachen-msft]

Hi @vwiles84 ,

Here is a resolved case which is similar to yours for your reference.

https://community.powerbi.com/t5/Desktop/Extracting-Data-from-API-and-transform-into-tables/m-p/5696...

[vwiles84]

I tried that one and got an error: 

So i'm not sure if I entered in the incorrect syntax for the expandall command as the example stated ExpandAllQuandI, which is not what I am trying to use so I changed it to just ExpandAll.  Then this is the error I get.

[parry2k]

@vwiles84 seems like API is returning different type of data for each name, I believe you need to break it down into differen tables (based on name) and then expand it from there.

Like one table will be donation, one for product classes, one for employees as each of these names are sending different kind of data.

[vwiles84]

Can i do that in power query or do I have to break down the API call into levels.

[parry2k]

@vwiles84 I will do it in power query. Right-click on your result set what you showed, and then select "Reference" it will create another query with the resultset, filter on the employee , and do the similar for other names, reference and then filter on next name and then you can expand individual queries.

[vwiles84]

When I right click, I don't have reference. I only have COpy, Drill down and add as new query. When I add as a new query, I get the following: