Forum Discussion
Open Links To Excel Files As Dataset
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
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-power-query/
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
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
