Forum Discussion

jasondrummond's avatar
jasondrummond
Frequent Visitor
4 years ago
Solved

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

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 d...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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's avatar
jasondrummond
Frequent Visitor
4 years ago

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's avatar
    johnt75
    Super User
    4 years ago

    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