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]

Have to sign off now my new frend. It is almost midnight here. Thanks again. VETY Much appreciated. Please feel free to delete what posts in this conversation are not relevenat for the messageboard.

 

[smozgur]

Have to sign off now my new frend. It is almost midnight here. Thanks again. VETY Much appreciated. Please feel free to delete what posts in this conversation are not relevenat for the messageboard.

You're welcome. Glad to hear it helps.

This is a good thread that contains many M functions explained and none of the posts are irrelevant. However, when you have a chance, please mark a post as the solution as it will help future readers to find this question has a solution. I would mark post #2 since it has a generic solution to this question.

 

[JeffGrant]

Hi Smozgur,

when I refreshed the data this morning the increase in speed was crazy. You have brought me so far in a few short hours. I am super impressed with your knowledge. Congratulations to you.

One more silly question.

With this part... ie the second let, How can I restrict the iteration count. This call, as is, pulls all the data, which is great, however, in another parts of the model, I need to the access the same data but only to a maximum iteration count of 4, not the default of 10. The reason I am pulling the data twice is because in the worksheets, it is just much easier/faster to pull the data a second time to perform mathematical functions rather than use VBA to loop through the entire data set, which can easily be tens of thousands of rows.

I was experimenting with If Then coding, couldn't quite get the syntax right though, but I feel that you probably have a faster way of achieving the same result.

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

cheers

 

[JeffGrant]

Hi Smozgur

Problem Solved . I found the solution with this article.

Number rows by group using Power Query

A reader asked if we can number rows by group in Power Query, similar to the PARTITION function in SQL of course there is, we just need a little code.

excelguru.ca

 

[smozgur]

Problem Solved

Great! Welcome to the M programming