none
How do I edit this Query - Dates RRS feed

  • General discussion

  • HI

    I want to be able to edit this query to retrieve the last 11 days past and 3 days forward. The field I want to use is the MeetingDate

    let
        Source = Json.Document(Web.Contents("https://api.tatts.com/sales/vmax/web/data/racing/")),
        RaceDays = Source[RaceDays],
        #"Converted to Table" = Table.FromList(RaceDays, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"MeetingDate", "CurrentDay", "Meetings"}, {"MeetingDate", "CurrentDay", "Meetings"}),
        #"Expanded Meetings" = Table.ExpandListColumn(#"Expanded Column1", "Meetings"),
        #"Expanded Meetings1" = Table.ExpandRecordColumn(#"Expanded Meetings", "Meetings", {"Abandoned", "MeetingId", "MeetingCode", "MeetingType", "VenueName", "WeatherChanged", "Races"}, {"Abandoned", "MeetingId", "MeetingCode", "MeetingType", "VenueName", "WeatherChanged", "Races"}),
        #"Expanded Races" = Table.ExpandListColumn(#"Expanded Meetings1", "Races"),
        #"Expanded Races1" = Table.ExpandRecordColumn(#"Expanded Races", "Races", {"FeatureRaceBonusActive", "FixedOdds", "Status", "RaceNumber", "RaceTime", "RaceName"}, {"FeatureRaceBonusActive", "FixedOdds", "Status", "RaceNumber", "RaceTime", "RaceName"}),
        #"Expanded FixedOdds" = Table.ExpandRecordColumn(#"Expanded Races1", "FixedOdds", {"HasFixedPrice"}, {"HasFixedPrice"})
    in
        #"Expanded FixedOdds"
    
    

    So from a prior question I know the function to use is

    DateTime.LocalNow() - #duration(14, 0, 0, 0).

    But where in the query do I do this?



    Taking my C# further


    • Edited by SaythJ Wednesday, April 12, 2017 4:42 AM
    Wednesday, April 12, 2017 4:40 AM

All replies

  • You first need to turn the MeetingDate in an actual date field: tab Transform - Date - Parse.

    Then you can filter and make sure to use 1 data type.

    My suggestion is to use date type (not datetime) and add lines to your query:

        #"Parsed Date" = Table.TransformColumns(#"Expanded FixedOdds",{{"MeetingDate", each Date.From(DateTimeZone.From(_)), type date}}),
        #"Filtered Rows" = Table.SelectRows(#"Parsed Date", each [MeetingDate] >= Date.AddDays(DateTime.Date(DateTime.LocalNow()),-11) and [MeetingDate] <= Date.AddDays(DateTime.Date(DateTime.LocalNow()),3))
    in
        #"Filtered Rows"

    Wednesday, April 12, 2017 5:37 AM