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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Imke,

         

        Well, I think it just might not be doable :smileysad: - I've tried to advice in the link you sent and I still get the same error message, as I also do with the local files.

         

        Thanks for your input anyhow!

         

        Sam