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?)

Our take

Navigating the complexities of bid item data from the Alaska DOT can be a challenge for civil engineers. Importing nested records into a single Excel table may seem daunting, but it holds significant potential for streamlining your estimates. By effectively integrating bidder information within project listings, you can enhance data accessibility and analysis. In this guide, we’ll explore whether it’s feasible to insert these records directly or if a dual-table approach with VBA might be more efficient.

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]

Read on the original site

Open the publisher's page for the full experience

View original article

Tagged with