You're not showing the most important part: how did you used the table as a parameter in Power Query? That is the source of error,  but can't tell you what you did wrong without seeing the query text.

Anyway, to create an external parameter, use a table reference like this:

FilePath = Text.From(Excel.CurrentWorkbook(){[Name="TblName"]}[Content]{0}[Column Name]),

Then you can replace the absolute path with the FilePath variable.

Note that the red part represents the first cell below the header row (first cell of the Data Body Range). You can set multiple parameters in the same table, then refer to them by simply changing the index number:

Param1 = Text.From(Excel.CurrentWorkbook(){[Name="TblName"]}[Content]{1}[Column Name]),

Param2 = Text.From(Excel.CurrentWorkbook(){[Name="TblName"]}[Content]{2}[Column Name]),

and so on.

You can set a dropdown in that table cell, and use a code that will use the vba Change Event, to refresh the table.

Use the macro recorder while manually refreshing the query you need, this will provide most of the code you need.

Hi Catalin,

I'm an Excel novice, and I know even less when it comes to Power Query.

I did not have a parameter table. I appreciate your solution, but all of this going way over my head.

Basically, what I want do is to set up a table with a list of URLs and have Power Query to retrieve the results.

Beyond that, if possible... is to have Power Query to iterate over the entire list and bring all of the results together in one table.

I did some research online and noticed that you have a Guru.com profile. Have you ever done work on Upwork (oDesk). I hired a guy that created some
Excel spreadsheets to retrieve data from various APIs. He' also has VBA skills but no Power Query.

I think I might need to create a small project for this solution. Let me know if you'd be interested.

Thanks!

Hi,

Because each link can potentially have a totally different structure, most probably they need to be processed with different Queries, each with its own set of steps and operations. When you change the source link from the dropdown, a code must refresh the corresponding query.

If the links will retrieve data that always have the same structure, then a single query will be enough.

I do have profiles on upwork and other such platforms, but for your project, if you don't have the time to create the solution yourself, you have to contact Mynda at My Online Training Hub <website@myonlinetraininghub.com> to request a quote for our paid services.

Thanks Catalin,

I actually found "most" of the answer that I was seeking for this particular issue by watching this video:

https://channel9.msdn.com/Events/TechEd/NorthAmerica/2014/DBI-B410#fbid=

I still would like to get more advanced with the drop-down list, but perhaps for a different issue I'm having with syncing Excel to Google Sheets.

What are the rates for your services? I'm a freelancer myself, and most of the people that I hire are between $15 - $20/hr range.

You will get a quote by mail if you request one. As you already know, you can hire people with 2-3 usd per hour, but if you want to get help from Chip Pearson for example, you will have to pay at least 150 usd per hour. Depends on what you need. Which one has the best price? Chip might finish in 1-2 hours, others may struggle for days to get the same result 🙂

To stay on topic, if you can prepare a more clear description of the problem, maybe we can help here on forum, it does not sound like a hard problem.

Ok Catalin, let's see what we can do here in the forum. Help me to understand your solution.

Again, what I'm trying to do is to set up a list of Google Sheets URLs in a table and have Power Query to retrieve the results. Each Google Sheet is a .CSV output. ideally, I'd like to have a drop down list where I can select the URL which will then automatically create a new query.

Thanks!

DigaloGuapo said
... I can select the URL which will then automatically create a new query.

Only Excel 2016 has the vba library objects to create a power query from vba, older versions are not able to create a query from VB.

How different are those csv files? They will have a different structure all the time, or the headers are the same, they only have new data?

Take a look again at this previous message, I believe you did not fully understood it:

Catalin Bombea said
Because each link can potentially have a totally different structure, most probably they need to be processed with different Queries, each with its own set of steps and operations. When you change the source link from the dropdown, a code must refresh the corresponding query.

If the links will retrieve data that always have the same structure, then a single query will be enough.

It's important to know what you want to do with that csv files, just saying that a new query must be created is not enough. That csv data needs to be reformatted? Data must be loaded in excel sheets, or just as connections?

Only Excel 2016 has the vba library objects to create a power query from vba, older versions are not able to create a query from VB.

How different are those csv files? They will have a different structure all the time, or the headers are the same, they only have new data?

I am using Office 365 ProPlus.

I'll be pulling similar data sets most of the time. However, I'd like to have the flexibility to retrieve data with different structures, in terms of headers and number of columns.

It's important to know what you want to do with that csv files, just saying that a new query must be created is not enough. That csv data needs to be reformatted? Data must be loaded in excel sheets, or just as connections?

I just need to retrieve the data and either load it to a new connection or new table. I do all of my formatting/data manipulation inside of Excel because it's simpler for me than Power Query.

If you choose Power Query to import data, and you make changes to the table produced by Power Query, that table will be updated when you refresh the query, and all the changes you made will be lost, that's why most of the operations needs to be done in Power Query. New columns can be added to the table, but all data transformations must be done in PQ, which has more powerful tools for data transformation than excel has.

If your files will have different number of columns, you should add a new query for each structure, because you might need different transformations/data manipulation for each type/structure-visualize-data of file.

If you want just one query, without data transformation, every time you change the source data, the result of the table will be updated too, old data will be replace with new data.

What's the purpose of processing these data files (the expected result)?

Thanks Catalin.

I'm using PQ almost exclusively to extract data. I know that the data shaping/transformation features are powerful, but that's way to complicated for me.

The goal here is to simulate a data connection between Excel and Google Sheets (why doesn't PQ have a built-in connector?).

For now, my best work-around appears to be BlockSpring connector for Excel

Hi Digalo,

You can use a user defined function in Power Query, to combine data from a list of URL's.

First, your file from Google sheets must be published as a csv, not HTML from file-Publish. This will provide the link you need. Whenever you have a new file, publish it, get the link and add it to a table in excel.

Add a blank query, with the following code, and name it GetURLData:

Then, add a new query from table (the table with the list of links):

As you can see, all you have to do is to add a custom column in your query from a table, and use that function created in the previous step:

=GetURLData([URL List])

Same queries can be found in the attached file.

If you have experience in VBA, you can use Google Sheets API to manipulate google sheets data: Google Sheets API v4

You can find useful libraries here: google sheets

Awesome! Thanks do much for your help, Catalin.

I will tinker around with this and see what I can do.

The VBA idea sounds interesting. I don't know VBA but I hired a guy on one of the job boards who created some API tools for me using VBA.

Thanks again for your help.