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

[wkeicher]

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

[camargos88]

@wkeicher ,

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

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

Ricardo

[wkeicher]

@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"

[wkeicher]

@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

[wkeicher]

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

could the issue be a date formatting issue?