Forum Discussion
PowerBI can do dynamic request to API with different parameters and union all results in one table?
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
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
5 Replies
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/Ef3cKxN6T2RIjdkXBMmLiPcBSKgqpvR0NLofz39bwMsQSA?e=nXof92
Hey Nolock
Using this column URL is possible to do a request (for the url) and union the results in the same table?
Thanks!
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
