Martinl
New member
- Joined
- Dec 2, 2016
- Messages
- 15
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 2016 MSO (16.0.9126.2259)
Hi Guys
In the past I have managed to navigate to a Sharepoint folder and then filter the lists to get a range of EXCEL files to download into power query to manipulate and then display in Excel.
However this time I am at the point of finding all the files I need but cannot see a way of opening them in PowerQuery.
I have access in SharePoi8nt and can download the files from there - one at a time, which is pretty useless however from PowerQuery I cannot see a way of getting the files even though I can see the file names.
My query so far is as below
Am I missing something here.
In the past I have managed to navigate to a Sharepoint folder and then filter the lists to get a range of EXCEL files to download into power query to manipulate and then display in Excel.
However this time I am at the point of finding all the files I need but cannot see a way of opening them in PowerQuery.
I have access in SharePoi8nt and can download the files from there - one at a time, which is pretty useless however from PowerQuery I cannot see a way of getting the files even though I can see the file names.
My query so far is as below
Code:
let
Source = SharePoint.Tables("http://sp13.emea.xxxxx.net/emea/EMDBS/Sales/", [ApiVersion = 15]),
#"Channel Programme" = Table.SelectRows(Source, each ([Title] = "Channel Programme")),
#"Expanded Items" = Table.ExpandTableColumn(#"Channel Programme", "Items", {"Title", "Folder"}, {"Items.Title", "Items.Folder"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Items", each [Items.Title] = "Benelux" or [Items.Title] = "EDG" or [Items.Title] = "EFS" or [Items.Title] = "EIB" or [Items.Title] = "EIS" or [Items.Title] = "EUL" or [Items.Title] = "Nordics"),
EDG = Table.SelectRows(#"Filtered Rows", each ([Items.Title] = "EDG")),
#"Expanded Items.Folder" = Table.ExpandRecordColumn(EDG, "Items.Folder", {"Folders"}, {"Items.Folder.Folders"}),
#"Expanded Items.Folder.Folders" = Table.ExpandTableColumn(#"Expanded Items.Folder", "Items.Folder.Folders", {"ListItemAllFields"}, {"Items.Folder.Folders.ListItemAllFields"}),
#"Expanded Items.Folder.Folders.ListItemAllFields" = Table.ExpandRecordColumn(#"Expanded Items.Folder.Folders", "Items.Folder.Folders.ListItemAllFields", {"Folder"}, {"Items.Folder.Folders.ListItemAllFields.Folder"}),
#"Expanded Items.Folder.Folders.ListItemAllFields.Folder" = Table.ExpandRecordColumn(#"Expanded Items.Folder.Folders.ListItemAllFields", "Items.Folder.Folders.ListItemAllFields.Folder", {"Folders"}, {"Items.Folder.Folders.ListItemAllFields.Folder.Folders"}),
#"Expanded Items.Folder.Folders.ListItemAllFields.Folder.Folders" = Table.ExpandTableColumn(#"Expanded Items.Folder.Folders.ListItemAllFields.Folder", "Items.Folder.Folders.ListItemAllFields.Folder.Folders", {"Folders"}, {"Items.Folder.Folders.ListItemAllFields.Folder.Folders.Folders"}),
#"Expanded Items.Folder.Folders.ListItemAllFields.Folder.Folders.Folders" = Table.ExpandTableColumn(#"Expanded Items.Folder.Folders.ListItemAllFields.Folder.Folders", "Items.Folder.Folders.ListItemAllFields.Folder.Folders.Folders", {"Files"}, {"Items.Folder.Folders.ListItemAllFields.Folder.Folders.Folders.Files"}),
#"Expanded Items.Folder.Folders.ListItemAllFields.Folder.Folders.Folders.Files" = Table.ExpandTableColumn(#"Expanded Items.Folder.Folders.ListItemAllFields.Folder.Folders.Folders", "Items.Folder.Folders.ListItemAllFields.Folder.Folders.Folders.Files", {"Name", "TimeLastModified"}, {"Items.Folder.Folders.Name", "Items.Folder.Folders.TimeLastModified"}),
#"Expanded Items.Folder.Folders.ListItemAllFields1" = Table.ExpandRecordColumn(#"Expanded Items.Folder.Folders.ListItemAllFields.Folder.Folders.Folders.Files", "Items.Folder.Folders.ListItemAllFields", {"File"}, {"Items.Folder.Folders.ListItemAllFields.File"})
in
#"Expanded Items.Folder.Folders.ListItemAllFields1"Am I missing something here.