Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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-...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.