Power Query use list of URLs as source

deLockloire

Board Regular
Joined
Apr 4, 2008
Messages
123
Office Version
  1. 365
Platform
  1. Windows
Hey,

I have a gazillion of files that have data that I'd like to load in a central file. In the central file, I have an excel table with the file path/url of those files. The paths/urls can change, hence the table. Can I utilize that table to load in the source files without having to set up connections one by one?

The files have the same structure, and all tables I'd like to load in and append / merge have the same structure and naming convention.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Yes this is very feasible. You will need to create two Power Query codes. One of these codes will be a Custom Function that you invoke into the other Power Query code. Lets start with the Custom Function Power Query Code first:

You want to treat this code like you are getting one specific Excel file that is also in your Table of paths/urls. Here is an example:

Power Query:
let
    Source = Excel.Workbook(File.Contents("C:\Mr Excel\MrExcelFolderPath\OtherFolderName\Current\deLockloire.xlsx"), null, true),
    Costdata_Sheet = Source{[Item="SheetNameFordeLockloire",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Costdata_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"



Once you are confident that this code could also be replicated with the other paths/urls, we can convert this code in a custom function like below:



Power Query:
(fileName as text) =>
let
    Source = Excel.Workbook(File.Contents(fileName), null, true),
    Costdata_Sheet = Source{[Item="SheetNameFordeLockloire",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Costdata_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"


Then from here, we now invoke this custom function into another Power Query code that gets your table in your current Excel file. Lets assume this table is called Table1.

To get the list of paths/urls for Table1, we would start with the following code:


Power Query:
= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]


From there we go to Add Column > Invoke Custom Function and then choose the function query name and the file path/url column.



It is then going to ask you a question about data privacy. Click Continue and then check the "Ignore Privacy Levels checks for this file." and click Save.



After this, you should have every table that is within the Table1 file paths/urls and you will be able to check each file and see if it works. From there you would just expand the table and you will have everything consolidated into your central file.



Your final code would like this (assuming the Custom Function we created is named Costdata.


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Invoked Custom Function" = Table.AddColumn(Source, "Costdata", each Costdata([File Path]))
in
    #"Invoked Custom Function"

Hope this helps!
 
Upvote 0
Solution
Sorry for the late reply. I did it manually, because there were some time constraints, but I'd like to learn how to do it next time. So I tinkered with your solution and I have a couple of questions:

Power Query:
    Source = Excel.Workbook(File.Contents("C:\Mr Excel\MrExcelFolderPath\OtherFolderName\Current\deLockloire.xlsx"), null, true),

If I input a specific URL from my list, it gives me an error message that "DataFormat.Error: The supplied file path must be a valid absolute path." The same URL works in other queries if you input them as source. E.g.,

Code:
Source = Excel.Workbook(Web.Contents("specific URL"), null, true),

Power Query:
    Costdata_Sheet = Source{[Item="SheetNameFordeLockloire",Kind="Sheet"]}[Data],

I guess this would mean that from the file you specified (deLockloire.xlsx), I navigate to the Sheet named SheetNameFordeLockloire, right? Would that also mean that if I want a table, I need to replace this with

Code:
Table_Reference = Source{[Item="Table_I_Want",Kind="Table"]}[Data],

Also, what would I need to change if the list contained a bunch of scattered, but uniformly formatted .csv files that I want to append? (Because I have that list as well).


I'll get back to this once I can make your first step work.


To get the list of paths/urls for Table1, we would start with the following code:

Power Query:
= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

Would this be the Source? I.e.,

Code:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

Also, does Table1 contain the list of paths? And what is the list of paths is just one column of the table?

Thanks!
 
Upvote 0

Forum statistics

Threads
1,226,267
Messages
6,189,940
Members
453,583
Latest member
Ok_category1816

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top