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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors