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]

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

[wkeicher]

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

[camargos88]

@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

[wkeicher]

@camargos88 

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

[camargos88]

@wkeicher ,

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

Ricardo

[wkeicher]

@camargos88 

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

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

[camargos88]

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

[wkeicher]

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

[camargos88]

@wkeicher ,

I copied your code and got this:

If possible, upload your pbix without your key.

Ricardo

[wkeicher]

I get this also in the preview, but when I try to "Close & Apply" I get the Mashup error.

I notice your date format is different from mine.

[wkeicher]

I just created a Clean PBIX file called test. Added the code and recieved the same error.

How do I upload a file?

[camargos88]

@wkeicher ,

You can use onedrive or google drive.

After upload, just get the shareable link.

Ricardo

[wkeicher]

@camargos88 

https://drive.google.com/open?id=1CMdibJLjImPyMCSCdJsHARQaBXd1GovO 

Thanks

[wkeicher]

@camargos88 

Could this be a regional setting issue with the use of .LocalNow ?

[camargos88]

@wkeicher ,

I couldn't download your file.

Ricardo

[wkeicher]

Try again ?

https://drive.google.com/open?id=1CMdibJLjImPyMCSCdJsHARQaBXd1GovO

@camargos88 

[camargos88 · 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"

[wkeicher]

@camargos88 ,

The Preview comes up fine...once I "Close and Apply" I get the same error. So strange that it works for you.

This is based on a completely clean and new PBIX file.

[camargos88]

@wkeicher ,

What is your pbi version ? Is it updated ?

Ricardo

[wkeicher]

@camargos88 ,

Version: 2.79.5768.721 64-bit (March 2020)