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]

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?

Not quite, Because I subscribe to the data, I do connect to a website via the API using a java app that I had developed two years ago. That java app, uses the csv files to give me the starting point. Then I do some majc wand analysis in my predeicative model in Excel and use a lot of VBA and PQ.

This project is about replacing the JAVA app because the csv data is going away and I want to keep it in EXCEL so I can control it and change it as necessary without relying on JAVA programmers.

 

[smozgur]

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

Unfortunately I can't provide help without knowing what you are doing but only posting an error. Of course I can guess that you changed GetMeetingList to GetMeetings that actually returns a list now, so it fails in the next step of the query that tries to extract a table field, but you know what I mean.

I can see that you have a good start and understanding of M and PQ at this point. You can start new threads for additional questions, because this way, discussing a project in a single thread turns to be a "complete project work" other than helping you and future readers of this subject. Please feel free to keep asking on the board. There are many Power Query and M experts who'll be willing help.

I hope I was able to help so far.

 

[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

downloaded GetForm records that is... The aim is to get all off the GetForm data for all of the races into one single excel table.

 

[smozgur]

downloaded GetForm records that is... The aim is to get all off the GetForm data for all of the races into one single excel table.

If you look at the last column which is generated at the TableFromJson step, you will see a little button at the header - two arrows - and you can click on it to expand records into new columns:

After expanding that records, you will see another record field:

And you will do the same to expand these records by using the same button.

And you will then have ALL data. However, you will still need to shape/transform the final data table as you wish, either in PQ or in Excel. Since you can now load this entire table into a worksheet, I think it won't be a problem after this point.

You can see the function used for this record-expanding action in the step formula, Table.ExpandRecordColumn() function, and examine it. The PQ user interface already does a great job. Finally, just like we all used Macro Recorder to get better in VBA, M could be also learned at a deeper level by using the PQ user interface.

Hope this helps.

 

[JeffGrant]

When I expand the various table columns, there is a lot more data that I have not seen before because it is not available in the csv files.

Looks like I just need ot remove unwanted data and load to an excel table then I am alsmost at the reults of teh JAVA App

Looking Excellent Sir.. You are a gentleman and a scollar.

 

[smozgur]

When I expand the various table columns, there is a lot more data that I have not seen before because it is not available in the csv files.

Looks like I just need ot remove unwanted data and load to an excel table then I am alsmost at the reults of teh JAVA App

Right. However, if I were you, I would select only the necessary columns when you expand the Record column. As soon as you click on the expand button that I explained previously, you will see a list of columns and you can deselect the ones that you won't need.

 

[smozgur]

Right. However, if I were you, I would select only the necessary columns when you expand the Record column. As soon as you click on the expand button that I explained previously, you will see a list of columns and you can deselect the ones that you won't need.

Meaning, Instead of removing afterward, I would not expand them in the first place.

 

[JeffGrant]

Just one more sorry. The date column. when i change the date type to either date or date/time from text i get an error.

the date looks like / Date(16865784000+1000)/ when I need a normal date format.

what do i need to so?

 

[smozgur]

Just one more sorry. The date column. when i change the date type to either date or date/time from text i get an error.

the date looks like / Date(16865784000+1000)/ when I need a normal date format.

what do i need to so?

It is a Unix Timestamp and you should convert it to a date.

Click AddColumn->Custom Column, and enter the following custom column formula:

= #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, Number.FromText(Text.Range([Date], 6, 13))/1000)

This will extract the timestamp as text from the Date column (13 chars starting from 6th), and then convert it to a date value.

Then you can do the same for other date fields. I believe the DateStamp field is much reliable since it contains the time value as well.

 

[JeffGrant]

ahhhh...now that looks like somethign that i can recognise. thanks man