none
JSON returns success not data RRS feed

  • Question

  • HI

    I am trying to access a json api from this web service described here http://media.tatts.com/documentations/IData/IData.html#Data/Racing

    Using file JSON I get a successful response, but I do not receive data just a successful response.

    = Json.Document(Web.Contents("https://api.tatts.com/sales/vmax/web/data/racing/"))

    If I travel along adding other data attributes as per API I get the same thing successful results but no data. How do I get powerquery to return the data after it has returned successful?

    Further I know the data is there as I can show with this successful query if I grab it with Python.

    As this jupyter notebook shows.

    Edit

    Even when I process the data and write it to disk for it and then load it, it still only returns success and no data.


    • Edited by SaythJ Thursday, April 6, 2017 3:32 AM
    Thursday, April 6, 2017 12:34 AM

Answers

  • The data is in the nested list: you can drill down into the list, turn the result into a table and than you need to perform a series of expanding nested lists/records columns, as illustrated in this video.
    • Marked as answer by SaythJ Thursday, April 6, 2017 4:55 AM
    Thursday, April 6, 2017 4:29 AM

All replies

  • The data is in the nested list: you can drill down into the list, turn the result into a table and than you need to perform a series of expanding nested lists/records columns, as illustrated in this video.
    • Marked as answer by SaythJ Thursday, April 6, 2017 4:55 AM
    Thursday, April 6, 2017 4:29 AM
  • Is there a way to save the import steps so i can get the new data daily?

    Thanks very much for the video.


    Taking my C# further

    Thursday, April 6, 2017 4:55 AM
  • Sure, during the recording of the video, the following code was created. You can copy/paste this code if you create a new query, go to the advanced editor and replace the default code by the copied code.

    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"

    Thursday, April 6, 2017 5:36 AM