edmunds | Edmunds.com Ruby API Wrapper | REST library

First thing first to select the first drop down item you need use cars[1] instead cars[0] because it is already selected and disabled.

To get the text from second dropdown you need to select the first dropdown item first.

So your code will be like

Perhaps there is a better solution, but you could use the parameter optionsAfterRender in the Options binding in order to modify the tag:

As you didn't add own code, I content myself only to the core facts of your post demonstrating an approach using the dynamic array features of MS 365 via the Filter() function, organized in a Let-function container which allows a clear structure avoiding some redundancies and enables to define expressions ("variables") together with their contents in pairs of arguments.

Needed steps

You are referring to an existing data sheet (assuming columns A:C), so try the following steps:

• Write your textbox value into a named cell (e.g. $D$1, named "Number"),
• Enter the following formula into cell $D$2:

The problem you are facing is that there is not a distinct table for each vehicle. There is one table shared by several. If you do a new web query (Query -> Web) and enter a URL like https://www.edmunds.com/lexus/ls-460/2016/cost-to-own/?style=401580678 you'll see the collection of tables. (I'm quite sure you already know this from your info above.) But if you look within the Suggested Tables, you'll see they all contain information for multiple 2016 models. I think you'll need to pull the entire table with the information you want (I think you want Table 1) and then parse that table once you get it. You can use any one of the vehicles from that table to get to the table. (It appears to me, from a quick glance, that each table is all of the vehicles from the year group).

Call your function from a new column in your first query with = Table.AddColumn(#"Expanded option", "Custom.1", each fnGetEdmunds(Text.From([loc])&"?style="&Text.From([Style]))). You'll get some errors because some rows don't have Styles to find pages with the Cost to Own and some of the pages don't have tables with Cost to Own. So you'll have to handle those errors.

Here's M code:

Use sed. You regex seems to be extended regex.

Check the following:

• \b(?:0?[1-9]|1[012])(?:[-/.](?:0?[1-9]|[12][0-9]|3[01]))?[-/.](?:19|20)?\d\d\b should handle the "dates (e.g. 1/31/2020, 3/20)" case
• (?i)\bEnd(?: DATE|(?:ing)?)\b should handle the "strings like "Ending", "ENDING", "END", "end", "End", "END DATE" but NOT the "end" from strings that have "endemic" in them like the last one" case
• ([\s»]){2,} should handle the "double spaces e.g. " "" case.

Combining all:

You can just pick the 2nd element of the "estimate" column of the broom output:

You can use getall() method to return a list with all results.

By the way after getting list of results, you need to modify your code a bit to actually map between name and it's definition.

After getting the list, you can simply iterate over it then write to csv file. Below is an example: