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 similar when I have files linked in a Sharepoint folder (as explained here) but I can't seem to find a way to do this for a list of links.

 

It might not even be possible, but just wondering if anyone might have a solution?

 

TIA,

 

SamB

Need Help

7 Replies

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