Open Links To Excel Files As Dataset

[Anonymous]

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

[ImkeF]

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]

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]

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]

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

[ImkeF]

Have you tried my method on you local files? What did the error-message say?

If you want to continue with Sharepoint, I recommend this: https://www.poweredsolutions.co/2019/04/04/connecting-to-files-in-sharepoint-onedrive-with-power-bi-... 

[Anonymous]

Hi Imke,

Well, I think it just might not be doable  - 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

[mattlancs]

Hi there, I was facing the same issue, having a list of links to Sharepoint-hosted Excel files that I wanted to open in Power Query. I managed to get it working in the end, with a solution inspired by this post. Instead of trying to use the magical Excel-opening function, you have to use the Web-opening function. In the example above that means changing:

ImkesStep = Table.AddColumn(#"Changed Type", "Custom", each Excel.Workbook(File.Contents([Links])))

 to

ImkesStep = Table.AddColumn(#"Changed Type", "Custom", each Web.Contents([Links]))

That seems to work for me.

Cheers,

Matt