Query a API based on cell value and load response

[sdjelal]

I have an existing workbook which has both number/epoch dates and text. Based on the number in each row, I am looking to query an API that returns the subsequent columns. However, I want to return only the reward data based on each day (displayed as epoch time). Although, I consider myself an average excel user, I do not really know how to use power query. I'm using Excel 2021. Appreciate all help.

	01/03/22	02/03/22	03/03/22
67907
69828
294291

For e.g., below is the API code for the first row item = 67907.

GET "https://api-apollo.pegaxy.io/v1/game-api/race/history/pega/67907"
Response{
"status": true,
"data": {
"Reward": "e.g. 26",
}
}

[v-jingzhang]

Hi @sdjelal 

What is epoch time? I called the API in Power Query and got a list of records. Every record has a reward value in it. Which one is the reward value to be extracted for this row 67907?

Best Regards,
Community Support Team _ Jing

[sdjelal]

Hi, thanks for the reply. I was starting to get worried. I suppose, more explaining is required on my behalf, apologies for that. 

Once the initial API is called, converted to table and expanded, the only two data entries that are required are record and race (figure 1). Then, I expand race and only require schedule (figure 2), which then reveals the epoch times. I, then, converted the epoch times via power query to regular time and date format (figure 3). 

This is actually where I require the help. I would like to reach this data based on each ID within the table and then be able to organise/categorise the data (rewards) based on the dates.

Once again, apologies for not being clear.

Figure 1

Figure 2

 

 Figure  3

[v-jingzhang · Accepted solution]

Hi @sdjelal 

Thank you for explaining. The Figure 3 is only for one ID, right? Based on Figure 3, you can first extract date from schedule column by converting it to date data type or adding a new column to get dates. 

Then convert this query into a custom function with id as a parameter. Add "(id as number)=>" on the first row and replace the static id number with the parameter id. 

Prepare another table which has all IDs in a column. Go to Add Column > Invoke Custom Function, under Function query box, select the function query created in previous step. And select id column to provide data for the id parameter. This will bring you a new table column. It will invoke the custom function for every row. After that, expand the table column to have reward and schedule values for all IDs. 

Then you can apply the data, go to Power BI Desktop and use Matrix visual to display the result. Put ID on rows and date on columns. Put reward in Values well and select a proper aggregation type for it. 

Hope this is helpful. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.