Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wkeicher
Helper III
Helper III

create a function to get start and end today minus 365 days, then pass the dates into API

Need Help in a two step process:

1) Create a dynamic start and end date the end date being today and the start date being today minus 365 days

2) Pass the start date and end date into an API with each date separated by comma and space

a) API format =https://api.sportsdata.io/v3/nba/projections/json/DfsSlatesByDate/2019-03-06,%202020-03-05?key=this is mykey

Thanks,

Wayne

1 ACCEPTED SOLUTION

@wkeicher ,

 

Try this one:

 

let
Source = List.Dates(Date.AddDays(DateTime.Date(DateTime.LocalNow()), -98), 10, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Dates"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each Json.Document(Web.Contents("https://api.sportsdata.io/v3/nba/projections/json/DfsSlatesByDate/"& Date.ToText([Dates], "yyyy-MM-dd") &"?key=KEY"))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"SlateID", "Operator", "OperatorSlateID", "OperatorName", "OperatorDay", "OperatorStartTime", "NumberOfGames", "IsMultiDaySlate", "RemovedByOperator", "OperatorGameType", "SalaryCap", "SlateRosterSlots", "DfsSlateGames", "DfsSlatePlayers"}, {"SlateID", "Operator", "OperatorSlateID", "OperatorName", "OperatorDay", "OperatorStartTime", "NumberOfGames", "IsMultiDaySlate", "RemovedByOperator", "OperatorGameType", "SalaryCap", "SlateRosterSlots", "DfsSlateGames", "DfsSlatePlayers"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"SlateRosterSlots", "DfsSlateGames", "DfsSlatePlayers"})
in
#"Removed Columns"



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

24 REPLIES 24

@wkeicher ,

 

Are you able to get the data from browser ? Please, try it. 

Also clean your pbi cache (File -> Options -> Clear Cache.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@camargos88 

Thank you for your assistance. I have another request for help.

 

I would like to Pass the all the returned DfsSlatePlayers.PlayerID from the Previous Query into my New Query below

 

Prevoius Query

let
Source = List.Dates(Date.AddDays(DateTime.Date(DateTime.LocalNow()), -98), 1, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Dates"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each Json.Document(Web.Contents("https://api.sportsdata.io/v3/nba/projections/json/DfsSlatesByDate/"& Date.ToText([Dates], "yyyy-MM-dd") &"?key=MYKEY"))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"SlateID", "Operator", "OperatorSlateID", "OperatorName", "OperatorDay", "OperatorStartTime", "NumberOfGames", "IsMultiDaySlate", "RemovedByOperator", "OperatorGameType", "SalaryCap", "SlateRosterSlots", "DfsSlateGames", "DfsSlatePlayers"}, {"SlateID", "Operator", "OperatorSlateID", "OperatorName", "OperatorDay", "OperatorStartTime", "NumberOfGames", "IsMultiDaySlate", "RemovedByOperator", "OperatorGameType", "SalaryCap", "SlateRosterSlots", "DfsSlateGames", "DfsSlatePlayers"}),
#"Expanded DfsSlateGames" = Table.ExpandListColumn(#"Expanded Custom1", "DfsSlateGames"),
#"Expanded DfsSlatePlayers" = Table.ExpandListColumn(#"Expanded DfsSlateGames", "DfsSlatePlayers"),
#"Expanded DfsSlateGames1" = Table.ExpandRecordColumn(#"Expanded DfsSlatePlayers", "DfsSlateGames", {"SlateGameID", "SlateID", "GameID", "OperatorGameID", "RemovedByOperator", "Game"}, {"DfsSlateGames.SlateGameID", "DfsSlateGames.SlateID", "DfsSlateGames.GameID", "DfsSlateGames.OperatorGameID", "DfsSlateGames.RemovedByOperator", "DfsSlateGames.Game"}),
#"Expanded DfsSlateGames.Game" = Table.ExpandRecordColumn(#"Expanded DfsSlateGames1", "DfsSlateGames.Game", {"GameID", "Season", "SeasonType", "Status", "Day", "DateTime", "AwayTeam", "HomeTeam", "AwayTeamID", "HomeTeamID", "StadiumID", "Channel", "Attendance", "AwayTeamScore", "HomeTeamScore", "Updated", "Quarter", "TimeRemainingMinutes", "TimeRemainingSeconds", "PointSpread", "OverUnder", "AwayTeamMoneyLine", "HomeTeamMoneyLine", "GlobalGameID", "GlobalAwayTeamID", "GlobalHomeTeamID", "PointSpreadAwayTeamMoneyLine", "PointSpreadHomeTeamMoneyLine", "LastPlay", "IsClosed", "GameEndDateTime", "HomeRotationNumber", "AwayRotationNumber", "Quarters"}, {"DfsSlateGames.Game.GameID", "DfsSlateGames.Game.Season", "DfsSlateGames.Game.SeasonType", "DfsSlateGames.Game.Status", "DfsSlateGames.Game.Day", "DfsSlateGames.Game.DateTime", "DfsSlateGames.Game.AwayTeam", "DfsSlateGames.Game.HomeTeam", "DfsSlateGames.Game.AwayTeamID", "DfsSlateGames.Game.HomeTeamID", "DfsSlateGames.Game.StadiumID", "DfsSlateGames.Game.Channel", "DfsSlateGames.Game.Attendance", "DfsSlateGames.Game.AwayTeamScore", "DfsSlateGames.Game.HomeTeamScore", "DfsSlateGames.Game.Updated", "DfsSlateGames.Game.Quarter", "DfsSlateGames.Game.TimeRemainingMinutes", "DfsSlateGames.Game.TimeRemainingSeconds", "DfsSlateGames.Game.PointSpread", "DfsSlateGames.Game.OverUnder", "DfsSlateGames.Game.AwayTeamMoneyLine", "DfsSlateGames.Game.HomeTeamMoneyLine", "DfsSlateGames.Game.GlobalGameID", "DfsSlateGames.Game.GlobalAwayTeamID", "DfsSlateGames.Game.GlobalHomeTeamID", "DfsSlateGames.Game.PointSpreadAwayTeamMoneyLine", "DfsSlateGames.Game.PointSpreadHomeTeamMoneyLine", "DfsSlateGames.Game.LastPlay", "DfsSlateGames.Game.IsClosed", "DfsSlateGames.Game.GameEndDateTime", "DfsSlateGames.Game.HomeRotationNumber", "DfsSlateGames.Game.AwayRotationNumber", "DfsSlateGames.Game.Quarters"}),
#"Expanded DfsSlatePlayers1" = Table.ExpandRecordColumn(#"Expanded DfsSlateGames.Game", "DfsSlatePlayers", {"SlatePlayerID", "SlateID", "SlateGameID", "PlayerID", "PlayerGameProjectionStatID", "OperatorPlayerID", "OperatorSlatePlayerID", "OperatorPlayerName", "OperatorPosition", "OperatorSalary", "RemovedByOperator", "Team", "TeamID", "OperatorRosterSlots"}, {"DfsSlatePlayers.SlatePlayerID", "DfsSlatePlayers.SlateID", "DfsSlatePlayers.SlateGameID", "DfsSlatePlayers.PlayerID", "DfsSlatePlayers.PlayerGameProjectionStatID", "DfsSlatePlayers.OperatorPlayerID", "DfsSlatePlayers.OperatorSlatePlayerID", "DfsSlatePlayers.OperatorPlayerName", "DfsSlatePlayers.OperatorPosition", "DfsSlatePlayers.OperatorSalary", "DfsSlatePlayers.RemovedByOperator", "DfsSlatePlayers.Team", "DfsSlatePlayers.TeamID", "DfsSlatePlayers.OperatorRosterSlots"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded DfsSlatePlayers1", {{"NumberOfGames", type text}}, "en-US"),{"Operator", "OperatorName", "NumberOfGames"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Slate")
in
#"Merged Columns"

New Query

let
Source = List.Dates(Date.AddDays(DateTime.Date(DateTime.LocalNow()), -98), 1, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Dates"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each Json.Document(Web.Contents("https://api.sportsdata.io/v3/nba/projections/json/PlayerGameProjectionStatsByPlayer/"& Date.ToText([Dates], "yyyy-MM-dd") &"/20000441(DfsSlatePlayers.PlayerID)?key=MYKEY"))),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom2", "Custom", {"StatID", "TeamID", "PlayerID", "SeasonType", "Season", "Name", "Team", "Position", "Started", "FanDuelSalary", "DraftKingsSalary", "FantasyDataSalary", "YahooSalary", "InjuryStatus", "InjuryBodyPart", "InjuryStartDate", "InjuryNotes", "FanDuelPosition", "DraftKingsPosition", "YahooPosition", "OpponentRank", "OpponentPositionRank", "GlobalTeamID", "FantasyDraftSalary", "FantasyDraftPosition", "GameID", "OpponentID", "Opponent", "Day", "DateTime", "HomeOrAway", "IsGameOver", "GlobalGameID", "GlobalOpponentID", "Updated", "Games", "FantasyPoints", "Minutes", "Seconds", "FieldGoalsMade", "FieldGoalsAttempted", "FieldGoalsPercentage", "EffectiveFieldGoalsPercentage", "TwoPointersMade", "TwoPointersAttempted", "TwoPointersPercentage", "ThreePointersMade", "ThreePointersAttempted", "ThreePointersPercentage", "FreeThrowsMade", "FreeThrowsAttempted", "FreeThrowsPercentage", "OffensiveRebounds", "DefensiveRebounds", "Rebounds", "OffensiveReboundsPercentage", "DefensiveReboundsPercentage", "TotalReboundsPercentage", "Assists", "Steals", "BlockedShots", "Turnovers", "PersonalFouls", "Points", "TrueShootingAttempts", "TrueShootingPercentage", "PlayerEfficiencyRating", "AssistsPercentage", "StealsPercentage", "BlocksPercentage", "TurnOversPercentage", "UsageRatePercentage", "FantasyPointsFanDuel", "FantasyPointsDraftKings", "FantasyPointsYahoo", "PlusMinus", "DoubleDoubles", "TripleDoubles", "FantasyPointsFantasyDraft", "IsClosed", "LineupConfirmed", "LineupStatus"}, {"StatID", "TeamID", "PlayerID", "SeasonType", "Season", "Name", "Team", "Position", "Started", "FanDuelSalary", "DraftKingsSalary", "FantasyDataSalary", "YahooSalary", "InjuryStatus", "InjuryBodyPart", "InjuryStartDate", "InjuryNotes", "FanDuelPosition", "DraftKingsPosition", "YahooPosition", "OpponentRank", "OpponentPositionRank", "GlobalTeamID", "FantasyDraftSalary", "FantasyDraftPosition", "GameID", "OpponentID", "Opponent", "Day", "DateTime", "HomeOrAway", "IsGameOver", "GlobalGameID", "GlobalOpponentID", "Updated", "Games", "FantasyPoints", "Minutes", "Seconds", "FieldGoalsMade", "FieldGoalsAttempted", "FieldGoalsPercentage", "EffectiveFieldGoalsPercentage", "TwoPointersMade", "TwoPointersAttempted", "TwoPointersPercentage", "ThreePointersMade", "ThreePointersAttempted", "ThreePointersPercentage", "FreeThrowsMade", "FreeThrowsAttempted", "FreeThrowsPercentage", "OffensiveRebounds", "DefensiveRebounds", "Rebounds", "OffensiveReboundsPercentage", "DefensiveReboundsPercentage", "TotalReboundsPercentage", "Assists", "Steals", "BlockedShots", "Turnovers", "PersonalFouls", "Points", "TrueShootingAttempts", "TrueShootingPercentage", "PlayerEfficiencyRating", "AssistsPercentage", "StealsPercentage", "BlocksPercentage", "TurnOversPercentage", "UsageRatePercentage", "FantasyPointsFanDuel", "FantasyPointsDraftKings", "FantasyPointsYahoo", "PlusMinus", "DoubleDoubles", "TripleDoubles", "FantasyPointsFantasyDraft", "IsClosed", "LineupConfirmed", "LineupStatus"})
in
#"Expanded Custom"

@camargos88 ,

 

Yes i had cleared Cache previously a few times. And Yes I can get the data from a web browser using the URL supplied in the Developer portal, it  returns the json data.

 

It turns out I may have exceeded my attempts limit today - I am now getting a different error message implying denial.

 

This is a frsutrating issue. I appreciate your help.

Wayne

What's interesting is that iAll I get in the report view is the following:

 

dates.png

could the issue be a date formatting issue?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors