Power Query use list of URLs as source

[deLockloire]

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.

 

[ExcelToDAX]

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:

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:

(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:

= 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.

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!

 

[ExcelToDAX]

Did this help you?