Power BI: How to Combine Multiple Excel Files from Multiple Websites

[jasondrummond]

Hi,

Fairly new to PowerBI but have been successfully combining multiple Excel files stored in a folder successfully.  This has been great as we can just drop a new file in to the folder and all data is updated (these files change daily but additional files are only added infrequently).

However these Excel files will now be available only from an internal website.  The issue is that the only way to access them is directly from individual links to each Excel spreadsheet (its an internal document management system - not SharePoint with only access via direct links).

I can individually connect to each spreadsheet however is it possible to create a list of URLs to combine files from in the same wayas the folders work that would mean adding a new file would just be updating a list somewhere?

Apologies if this is not clear - can provide further information. Any help greatly appreciated.

Thanks

[jasondrummond]

This has worked brilliantly - thanks very much for this.

[jasondrummond]

Hi,

Thanks for the update.  I created a table with the text values of 4 URLs as a test.

If I edit my existing query that looks at a folder for all the excel files and change the source to the URL table:

let
Source = Folder.Files(URL_Table),

Get the error

Expression.Error: We cannot convert a value of type Table to type Text.
Details:
Value=[Table]
Type=[Type]

If I try and use a data source of Web and change the Source to the table of URLs like below:

let
Source = Excel.Workbook(Web.Contents(URL_Table), null, true),

Get the error

Expression.Error: We cannot convert a value of type Table to type Text.
Details:
Value=[Table]
Type=[Type]

Any pointers would be helpful.

Thanks

[johnt75]

I think you need to import the table of URLs as a separate query, and then turn your existing query into a custom function which you can call using the URL column as a parameter

[johnt75]

You could create a custom function based on the transformation steps you already have but using a URL as a parameter. Then if you pull in a list of URLs from somewhere you can apply the custom function to the column with the URLs in it and then expand the resulting column. That should then have everything combined in the one table.

[jasondrummond]

Hi, sorry not sure how to create the custom function or parameter.  I'm guessing I can just create a text file with the list of URL's but not sure how to do that first step on function or parameter.  Is this a failry easy process?

Thanks

[johnt75 · Accepted solution]

Select your query and open Advanced Editor and then add a line in at the top like

( urlToGet as text ) =>

and that should turn the query into a function. You will need to replace the existing URL in your query with urlToGet.

Create a new query to get your list of URLs, from a text file or wherever. Add a step to that new query using the Invoke Custom Function button on the Add Column ribbon, and choose the function that you created. Expand the new table column and it should pull in all the data.

[jasondrummond]

Brilliant thanks for that - I'll have a go and let you know.