Forum Discussion

Anonymous's avatar
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...
Need Help
ImkeF's avatar
ImkeF
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's avatar
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's avatar
    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's avatar
      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