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.