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
camargos88
Community Champion
Community Champion

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!

Proud to be a Super User!



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

 

 

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

Proud to be a Super User!



@camargos88 

 

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

 

records.pngerrorcode.png

@wkeicher ,

 

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

 

Ricardo



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

Proud to be a Super User!



@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

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!

Proud to be a Super User!



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

 

@wkeicher ,

 

I copied your code and got this:

Capture.PNG

 

If possible, upload your pbix without your key.

 

Ricardo



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

Proud to be a Super User!



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.

 

view.png

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

 

How do I upload a file?

@wkeicher ,

 

You can use onedrive or google drive.

 

After upload, just get the shareable link.

 

Ricardo



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

Proud to be a Super User!



@camargos88 

 

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

@wkeicher ,

 

I couldn't download your file.

 

Ricardo



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

Proud to be a Super User!



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



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

preview1.pngapply.png

 

 

@wkeicher ,

 

What is your pbi version ? Is it updated ?

 

Ricardo



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

Proud to be a Super User!



@camargos88 ,

 

Version: 2.79.5768.721 64-bit (March 2020)

 

Helpful resources

Announcements
Europe Fabric Conference

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