Some Guidance please

[JeffGrant]

I have a java API that downloads and combines csv files from a data site, ready for import into excel.
Basically, the API gets a particular file, then loops through all of the lines in the file to pull down the relevant data for the day.

The data suppler also supplies the data as json files and they are making noises about stopping the supply of the csv files.

With PQ, i can see there are many videos about pulling a single data file with a known URL, but i have not seen anything that would effectively loop through an input list to create the URL as an input variable.

Is this possible?

I am a real novice with PQ, and a little above novice with VBA, so I can grab the jist of it without claiming be a professional. so I do understand the technical parts of what i am asking here.

I look forward to hearing from your guys soon.

 

[JeffGrant]

Just a quick note, I see that we are pulling the csv files. Ideally I need to get the data in JSON format because the data supplier is going to stop supplying the csv files at some point, I did mention this in post #1. I hope I am not waisting your time.

 

[JeffGrant]

to get JSON data in the second LET, the call is GETFORM instead of GETFORMTEXT.

The GETMEETINGLIST call is GETMEETINGS file

 

[smozgur]

Just a quick note, I see that we are pulling the csv files. Ideally I need to get the data in JSON format because the data supplier is going to stop supplying the csv files at some point, I did mention this in post #1. I hope I am not waisting your time.

In that case, an end-point (URL) that returns the data in JSON format is necessary.

 

[JeffGrant]

for two years I have been using the GetMeetingList and it supposed to return a csv file, but the data supplier is saying that it is a JSON file. I dont know for sure because the GetMeetingList data is not actually downloaded. The JAVA code, just uses as we are here. I have never actually saved it. Not that it matters here, but I will check with the data supplier and see if their descriptions are incorrect.

 

[JeffGrant]

As far as I can see on the data suppier website, the JSON calls are: GetMeetings & GetForm.

Can I please confirm this wit the data supplier and continue at another time. I dont want to waist your time.

 

[smozgur]

No, GetMeetingList returns JSON data, which is correct. You answered my question with GETFORM instead GETFORMTEXT.

If we update the query by using the GetForm endpoint instead, then we also need to use the Json.Document() function in the last step. Here is the updated query:

let
    // ...
    // All the same before this line

    // Fetch the JSON as the new table column
    TableFromJson = Table.AddColumn(WithRaceNumbers, "Json", each
            let
                URL = BaseDomain & "/GetForm/" & [Track] & "/" & Text.From([RaceNumbers]) & "/" & Today & "?ApiKey=" & APIKey,
                Result = Json.Document(Web.Contents(URL))
            in
                Result
        )
in
    TableFromJson

At this point, what you need is to create one sample final report that you'd like to get from this query. Then we can transform the data as necessary by looking at the desired result, otherwise one can lost in that much data.

Note: I think the generated data is not private, right? If that's the case, then I would instead use a real data table after table generation via API, and continue from that point as other Power Query members might be also interested to contribute maybe using more effective methods and other functions or others to learn.

 

[JeffGrant]

What do yu mean by " I think the generated data is not private, right?" Not private how? The data is supplied via a subscription service. It is what I do with after download that is exceptionally confidential

 

[JeffGrant]

not getting any syntax erros in the editor, but upon execution, am getting Expression.Error at the Results Step.

 

[smozgur]

What do yu mean by " I think the generated data is not private, right?" Not private how? The data is supplied via a subscription service. It is what I do with after download that is exceptionally confidential

Oh, so this data is not public. I thought that the "data" is public but the data provider sends the well-organized data to you since you are a subscriber without you getting into a website and collecting the data individually to create your tables yourself. So, you are saying that nobody can generate this data by looking at the related websites. Am I right?

 

[JeffGrant]

Fixed it... I changed an earlier line

I now have what you have. The meeting list with race numbers..

What I need is to transform ALL of the downloaded records into a single excel table, then we have achived about 95% of the JAVA app replacment