PowerBI can do dynamic request to API with different parameters and union all results in one table?

[gluizqueiroz]

I initiate a project about NCAA Football and I wana use public API's. 

Searching for public API's I found this one: https://api.collegefootballdata.com/games?year=2017&seasonType=postseason

The previous API returns some information about postseason games that happened in 2017. 

I wanna use on my projects games of regular and postseason and happened between 2003 and 2018. 

Is there any way to concatenate in one (even if contains a tons of rows) table various request to API using different parameters? I wanna do this various requests with different parameters using a automatic way, not manually. Something like:

https://api.collegefootballdata.com/games?year=2003&seasonType=regular

+

https://api.collegefootballdata.com/games?year=2003&seasonType=postseason

+ 

2004, 2005, 2006, 2007 ....

+

https://api.collegefootballdata.com/games?year=2018&seasonType=regular

+

https://api.collegefootballdata.com/games?year=2018&seasonType=postseason

[Nolock]

Hi @gluizqueiroz,

you can do it in the PowerQuery Editor.

Create a table with years, create a table with 2 rows (regular and postseason) and do a cross join.

let    
    Years = List.Generate(() => 2003, each _ <= 2019, each _ + 1),
    #"Converted to Table" = Table.FromList(Years, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Year"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "YearsTable", each SeasonTypes),
    #"Expanded YearsTable" = Table.ExpandTableColumn(#"Added Custom", "YearsTable", {"seasonType"}, {"YearsTable.seasonType"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded YearsTable", "URL", each "https://api.collegefootballdata.com/games?year=" & Text.From([Year]) & "&seasonType=" & [YearsTable.seasonType])
in
    #"Added Custom1"

You can find a file with the solution and code under https://nolockcz-my.sharepoint.com/:u:/g/personal/michal_nolock_cz/Ef3cKxN6T2RIjdkXBMmLiPcBSKgqpvR0N...

[gluizqueiroz]

Hey @Nolock 

Using this column URL is possible to do a request (for the url) and union the results in the same table?

Thanks!

[Nolock · Accepted solution]

Hi @gluizqueiroz,

yes, it is possible.

You have to call Web.Contents for every URL and then you expand the record into rows, see the code below.

let    
    Years = List.Generate(() => 2003, each _ <= 2019, each _ + 1),
    #"Converted to Table" = Table.FromList(Years, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Year"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "YearsTable", each SeasonTypes),
    #"Expanded YearsTable" = Table.ExpandTableColumn(#"Added Custom", "YearsTable", {"seasonType"}, {"YearsTable.seasonType"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded YearsTable", "URL", each "https://api.collegefootballdata.com/games?year=" & Text.From([Year]) & "&seasonType=" & [YearsTable.seasonType]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"URL", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "WebContent", each Json.Document(Web.Contents([URL]))),
    #"Expanded WebContent" = Table.ExpandListColumn(#"Added Custom2", "WebContent"),
    #"Expanded WebContent1" = Table.ExpandRecordColumn(#"Expanded WebContent", "WebContent", {"id", "season", "week", "season_type", "start_date", "neutral_site", "conference_game", "attendance", "venue_id", "venue", "home_team", "home_conference", "home_points", "home_line_scores", "away_team", "away_conference", "away_points", "away_line_scores"}, {"id", "season", "week", "season_type", "start_date", "neutral_site", "conference_game", "attendance", "venue_id", "venue", "home_team", "home_conference", "home_points", "home_line_scores", "away_team", "away_conference", "away_points", "away_line_scores"})
in
    #"Expanded WebContent1"

I attach a new version of the pbix file, you can see all the steps in the PowerQuery Editor: 644335_2.pbix

[gluizqueiroz]

Hey @Nolock.

Excellent sir! This is exactaly what I need!

I have poor knowlodge about Power Query and I am trying to learn about.

Thanks!

[Nolock]

Hi @gluizqueiroz,

great, glad to hear that.

If you don't mind please Accept it as the solution to help the other members find it more quickly.