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]

Now that is good idea, I should have known better. You are absolutely correct.

I have spent quite a bit of time today looking at List.Generate. I am getting the idea. Also went over your post that you referred me to. Thanks

 

[JeffGrant]

is there a way to indent text in PQ in the advanced editor to make things easy to read other than just pressing the space bar?

 

[smozgur]

is there a way to indent text in PQ in the advanced editor to make things easy to read other than just pressing the space bar?

Yes, simply use Tab, just like in the VBA editor.

 

[smozgur]

I have spent quite a bit of time today looking at List.Generate. I am getting the idea. Also went over your post that you referred me to. Thanks

I can say that you wouldn't need the List.Generate() function for this. I am going to send you another sample query soon.

 

[JeffGrant]

I tried that and it ddn't do anything. also have to use shift + enter to get a new line

 

[JeffGrant]

I agree, but I have to learn about M otherwise I don't get anywhere. I have to do a complete 180 in the way I think about programing
Did you keep a copy of the post details for yoru reference before deleting it?

 

[smozgur]

I tried that and it ddn't do anything. also have to use shift + enter to get a new line

Just to make sure. You are using the Advanced Editor opened by using the Advanced Editor button on the ribbon, right?

 

[JeffGrant]

correct, but it is working now.....maybe the focus wasnt working properly as i have three screens attached to the pc.

 

[smozgur]

Here is the update code.

let
    // URL constants
    Today = Date.ToText(Date.From(DateTime.LocalNow()), [Format="dd-MMM-yyyy"]),
    APIKey = "your-api-key-here",
    BaseDomain = "https://the-base-domain-here/api/formdataservice",

    // Main source URL
    URL = BaseDomain & "/GetMeetingList/" & Today & "?ApiKey=" & APIKey,
    // Main source - Json.Document returns a record
    Source = Json.Document(Web.Contents(URL)),
    // Actual sort is the Result field - extract the list
    Result = Source[Result],
    // Generate main table from the extracted list, it still contains records
    Table = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // Expand the records to get the actual data table finally
    Tracks = Table.ExpandRecordColumn(Table, "Column1", {"Date", "DateStamp", "MeetingId", "RaceCount", "RaceNumbers", "Resulted", "State", "TABMeeting", "Track", "TrackId"}),
    // Expand the RaceNumbers list values as new rows. This generates necessary number of rows for each track
    WithRaceNumbers = Table.ExpandListColumn(Tracks, "RaceNumbers"),
    // Fetch the CSV file as the new table column
    TableFromCSV = Table.AddColumn(WithRaceNumbers, "CSV", each 
            let 
                URL = BaseDomain & "/GetFormText/" & [Track] & "/" & Text.From([RaceNumbers]) & "/" & Today & "?ApiKey=" & APIKey,
                Result = Table.PromoteHeaders(Csv.Document(Web.Contents(URL),[Delimiter=","]))
            in
                Result
        )
in
    TableFromCSV

As far as I can see, no need to use the RaceCount, because the RaceNumbers is already returned as a list and could be expanded to create a new row for each race number. You'll see it in the comments.

You need to complete the APIKey and BaseURL identifiers.

Next, you'll decide how you will use the fetched Tables imported as the last column of this table.

 

[JeffGrant]

thanks. all i get back is the first track and race one only. is that what you are expecting?
Today there are 3 tracks with 12, 7 & 7 races respectively.