Use CSV column as custom parameter

[PhillipRothwell]

Hi I'm exploring the use of API to extract data for use in Power BI. Take the following as a particular call

https://mrurl.com/api/coursecode/33795/analytics/activity

s

I have a CSV containing a list of course codes similar to the one below

course_code

64726

43447

63794

29471

63803

What I'm aiming to do is loop through all values in that csv and inject these as a custom parameter into the above api request using  M in the custom query view. The resulting request would look like something below

https://mrurl.com/api/coursecode/{course_coderow1}/analytics/activity

https://mrurl.com/api/coursecode/{course_coderow2}/analytics/activity

https://mrurl.com/api/coursecode/{course_coderow3}/analytics/activity...... etc etc

Does anyone know the best way to approach this?

Thanks in advance 🙂

Phil

[v-yulgu-msft]

Hi @PhillipRothwell ,

As Imkef suggested here, you should firstly load the CSV file into desktop as a query, that holds all the variables/parameter and add a custom column to it where you make your API call, thereby referencing the column(s) with your variables where needed. That will return a colum with all the values from the different rows of your table that you can simply expand. 

Best regards,

Yuliana Gu