2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Power Query Help; importing nested records into one table (Possible? Worth the effort?)

Bid Item Data on the Alaska DOT website

Howdy! In civil engineering we have historical bid item data we pull from the DOT to estimate a good bid item price (how much the developer should ask for for a specific item). On the website it comes in this table above: note the two sections. This is how the data is formatted.

This is the query web link
https://dot.alaska.gov/procurement/awp/api/biddata?itemId=201.0001.0000&duration=2

Querying this into Excel, we get all of the data in the top box as a list of records; and then the second box, the list of bidders, we get as a column of lists of records within the first list of records (see column H below)

The list of Bidders, and where I would like to insert this data

The question is, is it feasible to take this list of records and insert it within the current table? Take that list and insert rows underneath each project row for the list of bidders? I think it's possible, but idk if it's worth the effort. I'm wondering if a better approach is to take this list and somehow make it a second table, then concatenate the two tables later with vba/some other way. I probably will pass this data through a vba script later sort it based on comparing values from our projects anyways. Thank you everyone!

submitted by /u/_Fat_Scout_
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#Excel alternatives for data analysis
#generative AI for data analysis
#big data management in spreadsheets
#conversational data analysis
#real-time data collaboration
#intelligent data visualization
#natural language processing for spreadsheets
#rows.com
#Excel compatibility
#row zero
#cloud-based spreadsheet applications
#financial modeling with spreadsheets
#Power Query
#bid item data
#nested records
#Alaska DOT
#Excel
#list of records
#civil engineering
#bidders