Forum Discussion

Anonymous

Not applicable

6 years ago

Open Links To Excel Files As Dataset

Hi all, I have an Excel table that has a list of links to other Excel files. What I would like to do is use Power Query to open all the links into a single dataset. I'm able to do something simil...

Community Champion

6 years ago

Hi Anonymous

Just add a column to the Excel table that contains the link like so:

Excel.Workbook(File.Contents([link])))

Where [link] is the reference to the column with the link to your Excel-Workbooks.

Anonymous
Not applicable
6 years ago
Hi Imke,

Thanks for the input. I think this is already done - so this is my table at the moment:

And this is the code:

let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Downloads\Example.xlsx"), null, true), Table2_2_Table = Source{[Item="Table2_2",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table2_2_Table,{{"Month", type datetime}, {"Links", type text}}) in #"Changed Type"
The second line looks like your suggestion, or am I missing something?

Thanks,

Sam
- ImkeF
  Community Champion
  6 years ago
  Yes, you missed to add the column:
  
  let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Downloads\Example.xlsx"), null, true), Table2_2_Table = Source{[Item="Table2_2",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table2_2_Table,{{"Month", type datetime}, {"Links", type text}}), ImkesStep = Table.AddColumn(#"Changed Type", "Custom", each Excel.Workbook(File.Contents([Links]))) in ImkesStep
  
  While this works on references to local files. You have to adjust it a bit to grab the contents from an online source.
  - Anonymous
    Not applicable
    6 years ago
    H Imke,
    
    Thanks for your help on this. The files are saved in a colleagues Sharepoint / OneDrive folder and I think that's why I'm now getting a 'DataFormat.Error: The supplied file path must be a valid absolute path' error. I've tried saving my file locally, but no joy.
    
    Thanks,
    
    Sam