Create Power BI Web Query based on data table

Question

I am using Power BI Desktop to create a web query to the stack overflow API. I want to obtain the number of questions asked on stack overflow for each user which is extracted from a prepopulated table of users in Power BI.

So I want something that will look like this:

Pre-populated Users:

• 6231494
• User2
• User3

StackOverflow Questions:

• 6231494: 5
• User2: 12
• User3: 10

Here is my current code for my web query in the advanced editor:

let
Source = Json.Document(Web.Contents("http://api.stackexchange.com/2.2/users/6231494/answers?order=desc&sort=activity&site=stackoverflow")),
items = Source[items],
#"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"owner", "is_accepted", "score", "last_activity_date", "creation_date", "answer_id", "question_id"}, {"Column1.owner", "Column1.is_accepted", "Column1.score", "Column1.last_activity_date", "Column1.creation_date", "Column1.answer_id", "Column1.question_id"}),
#"Expanded Column1.owner" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.owner", {"reputation", "user_id", "user_type", "profile_image", "display_name", "link"}, {"Column1.owner.reputation", "Column1.owner.user_id", "Column1.owner.user_type", "Column1.owner.profile_image", "Column1.owner.display_name", "Column1.owner.link"})
in
#"Expanded Column1.owner"

I basically need a way to set the current hardcoded ID (6231494) equal to the list of IDs from my predefined user table

Answer 1

Assume that you have a Users table with column ID as number:

You can make a few changes to your Questions query to turn it into a custom function in Power BI:

(id as number) =>
let
    Source = Json.Document(Web.Contents("http://api.stackexchange.com/2.2/users/" & Text.From(id) & "/answers?order=desc&sort=activity&site=stackoverflow")),
    items = Source[items]
    ...

(id as number) => is added to turn the query into a function; and the user ID is replaced with Text.From(id).

Here I renamed it to GetQuestions to keep it clear that it's a function:

You can then add a custom column to invoke the function:

Expand it and you shall find the columns from the original query:

And you can work with the data to get the results you want (e.g. number of questions).