Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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...
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
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!
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.