cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User V
Super User V

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

@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!
Ricardo

View solution in original post

24 REPLIES 24
Highlighted
Super User V
Super User V

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

Hi @wkeicher ,

 

Paste this code on Advanced Editor:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Start", each Date.AddDays(DateTime.FixedLocalNow(), -365)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "End", each DateTime.FixedLocalNow()),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each Json.Document(Web.Contents("https://api.sportsdata.io/v3/nba/projections/json/DfsSlatesByDate/"& DateTime.ToText([Start], "yyyy-MM-dd") &",%"& DateTime.ToText([End], "yyyy-MM-dd") &"?key=this")))
in
#"Added Custom2"

 

Note that you need to input the key on Added Custom 2.

 

Ricardo


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

Highlighted
Helper III
Helper III

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

@camargos88 

 

Thank you. I was a bit mistaken, it seems the API will only take one date at a time and is using the first date passed (start). How can I make multiple Calls using a date range and add the results to a single table? Can I create a loop based on the number of days (-365)?

 

Appreciate your help..!

 

 

Highlighted
Super User V
Super User V

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

@wkeicher ,

Try this code:

 

let
Source = List.Dates(Date.AddDays(DateTime.Date(DateTime.FixedLocalNow()), -365), 366, #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", "Date"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each Json.Document(Web.Contents("https://api.sportsdata.io/v3/nba/projections/json/DfsSlatesByDate/"& Date.ToText([Date], "yyyy-MM-dd") &"?key=this")))
in
#"Added Custom2"

 

Please mark it as a solution if it works.

 

Thanks,

 

Ricardo


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

Highlighted
Helper III
Helper III

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

@camargos88 

 

Making progress, however the records returned have an error after I extract values.

 

records.pngerrorcode.png

Highlighted
Super User V
Super User V

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

@wkeicher ,

 

Are you converting this column to text before extract the values ?

 

Ricardo


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

Highlighted
Helper III
Helper III

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

@camargos88 

 

Ok I resolved that issue by expanding rows versus extracting vales. The data come in fine in Preview mode

preview.png

however when I CLose & Apply from Power Query Editor and the data attempts to load, I get the following error (Image Below).

 

apply query changes.png

Highlighted
Super User V
Super User V

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

Hi @wkeicher ,

 

You have to expand all the list/recors or drop them before Close & Apply.

 

You can check all the fields here: https://sportsdata.io/developers/data-dictionary/nba

 

Ricardo


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

Highlighted
Helper III
Helper III

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

@camargos88 

Try expanding all and then also tried removing all columns that require expansion after the first expansion. Received the same error. Yes I have been referring to the API docs.

 

As you can see i also reduced the number of records to return.

 

let
Source = List.Dates(Date.AddDays(DateTime.Date(DateTime.FixedLocalNow()), -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=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"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"SlateRosterSlots", "DfsSlateGames", "DfsSlatePlayers"})
in
#"Removed Columns"

 

Highlighted
Helper III
Helper III

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

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
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors