Get and Transform Get data from SharePoint

Question

For some reason this is the biggest challenge I have when using Get and Transform.

Almost all our data is stored on SharePoint  and I often need to pick up the latest file.

There are lots of tips out there but none of these are working for me.

I can get to the SharePoint Folder using and see my filer but Binary is not available so I cant open it

I have tried going to  the Base folder and changing SharePoint.Table to SharePoint.Files but only a short list appears and then then there is an error which may be causing the list to stop or this may be a red herring

I have also tried changing the API to 14 from 15 but i get a different list which then makes it impossible for me to locate my file.

I have also tried Get Data - From Other Sources - From Web but as the tab I need is hidden (not Very Hidden) it does not appear in the list 

In any event I need to be able to manipulate the file name as it has a filename of My Data mmm yyy so if I just pull the data in straight it will not find the correct file next month.

Can someone point me in the right direction.

Answer 1

Hi Martin262,

Thanks for posting in the forum.

I tested on my end and I could see the files and content was listed as Binary. To access the SharePoint folder in Excel, I used Data tab, then clicked Get Data>From Folder File> From SharePoint Folder, as shown in the screenshot below.

When a prompt appeared to enter the address, I put in https://tenant.sharepoint.com/sites/SiteName and once it was connected, I could see the files.

Since you are getting the issue, may I request the following information to troubleshoot the issue?

1. Please provide us with a screenshot of Office 365 Product Information. To get this screenshot, open any Office app such as Word or Excel on your system, click File>Account, capture the screenshot of all the information mentioned under Product Information. (Note*: please remove any sensitive information from the screenshot such as email address or full name etc.).*
2. Please confirm if you are using SharePoint online or SharePoint server.
3. Provide some related screenshot for reference.
4. Try importing the data from a different site and check if there’s any difference.

In addition, you can consider using Export to Excel option available in SharePoint online list/library.

For detail steps, see Export to Excel from SharePoint

Thanks,

Neha

Answer 2

Hi Neha

Thank you for your swift response.

None of your images in this email came through ☹

I think there is an typo in your response I used Data tab, then clicked Get Data>From Folder> From SharePoint Folder I think you meant From File as From Folder is not an option for me here.

If I use this start point then I get this message – maybe this is the issue, I will check with our IT.

But not if I use other Get Data  options

1. As requested please see screenshot below

1. Please confirm if you are using SharePoint online or SharePoint server.

SharePoint server

1. Provide some related screenshot for reference.

My method for getting the data is:

Get Data>From Other Sources>From SharePoint List

I then select Transform Data and start to navigate through to the files on SharePoint

let

    Source = SharePoint.Tables("http://xxx.xx.xx.xx/emea/EMDCP/", [ApiVersion = 15]),

    #"0caf06c2-6fbb-4446-8258-7106cc4c2b01" = Source{[Id="0caf06c2-6fbb-4446-8258-7106cc4c2b01"]}[Items],

    #"Renamed Columns" = Table.RenameColumns(#"0caf06c2-6fbb-4446-8258-7106cc4c2b01",{{"ID", "ID.1"}}),

    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Title] = "GFK Market Dashboards")),

    #"Expanded Folder" = Table.ExpandRecordColumn(#"Filtered Rows", "Folder", {"Folders"}, {"Folder.Folders"}),

    #"Expanded Folder.Folders" = Table.ExpandTableColumn(#"Expanded Folder", "Folder.Folders", {"Files"}, {"Folder.Folders.Files"}),

    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Folder.Folders",{"Folder.Folders.Files"}),

    #"Expanded Folder.Folders.Files" = Table.ExpandTableColumn(#"Removed Other Columns", "Folder.Folders.Files", {"CheckInComment", "CheckOutType", "ContentTag", "CustomizedPageStatus", "ETag", "Exists", "Length", "Level", "MajorVersion", "MinorVersion", "Name", "ServerRelativeUrl", "TimeCreated", "TimeLastModified", "Title", "UIVersion", "UIVersionLabel", "Author", "CheckedOutByUser", "ListItemAllFields", "LockedByUser", "ModifiedBy", "Versions"}, {"Folder.Folders.Files.CheckInComment", "Folder.Folders.Files.CheckOutType", "Folder.Folders.Files.ContentTag", "Folder.Folders.Files.CustomizedPageStatus", "Folder.Folders.Files.ETag", "Folder.Folders.Files.Exists", "Folder.Folders.Files.Length", "Folder.Folders.Files.Level", "Folder.Folders.Files.MajorVersion", "Folder.Folders.Files.MinorVersion", "Folder.Folders.Files.Name", "Folder.Folders.Files.ServerRelativeUrl", "Folder.Folders.Files.TimeCreated", "Folder.Folders.Files.TimeLastModified", "Folder.Folders.Files.Title", "Folder.Folders.Files.UIVersion", "Folder.Folders.Files.UIVersionLabel", "Folder.Folders.Files.Author", "Folder.Folders.Files.CheckedOutByUser", "Folder.Folders.Files.ListItemAllFields", "Folder.Folders.Files.LockedByUser", "Folder.Folders.Files.ModifiedBy", "Folder.Folders.Files.Versions"})

in

    #"Expanded Folder.Folders.Files"

The screenshot would be too wide to do, but I have cut and pasted it below 

	2	{3616AFD8-ED3E-4339-8390-06D91392F83C},1,1	0	"{3616AFD8-ED3E-4339-8390-06D91392F83C},1"	TRUE	30332281	1	1	0	GFK Market Dashboard - FY19 Q1 - WE.xlsx	/emea/EMDCP/Launch Team/Competitive analysis tools/GFK Market Dashboards/WE/GFK Market Dashboard - FY19 Q1 - WE.xlsx	11/08/2019 13:55:39	11/08/2019 13:55:39		512	1.0	[Record]	null	[Record]	null	[Record]	[Table]
	2	{43A63BF9-C4E0-413F-819B-47F561ADEAF4},2,2	0	"{43A63BF9-C4E0-413F-819B-47F561ADEAF4},2"	TRUE	7774226	1	1	0	GFK Market Dashboard - FY19 Q1 - CISMEA + CEE.xlsx	/emea/EMDCP/Launch Team/Competitive analysis tools/GFK Market Dashboards/CISMEA + CEE/GFK Market Dashboard - FY19 Q1 - CISMEA + CEE.xlsx	13/08/2019 08:23:51	15/08/2019 13:03:28		512	1.0	[Record]	null	[Record]	null	[Record]	[Table]

1. Try importing the data from a different site and check if there’s any difference.

If I download the file to a regular folder on a normal server drive, which is set as a shared folder on my machine (i.e. z:/shared files/mlucas/), everything works as expected, its just my knowledge base with SharePoint files and Get and Transform

Once again thank you for your support with this.

Kind Regards

Martin

Answer 3

Hi Martin262,

Yes, I meant ‘From File’, thanks for mentioning, I’ll update this in my original post.

Regarding the issue, first thing I’ll suggest you update your Office applications, your Office version is behind some updates. The latest version of Semi-Annual Channel is Version 1902 (Build 11328.20438).

To know how to install updates, see Install Office updates

Once Office is updated, try performing the steps I mentioned in my last post, also the steps which you tried, check if either of that works. If you still get “Unable to connect Details "Access to the resources is forbidden" error, follow the steps below to rectify:

1. In Excel, click Data>Get Data> Data source settings.
2. Select on the site URL then click on “Edit Permission” in the Data source settings page.
3. Click on Edit.

          4. A new window will open, select Organizational account Or Microsoft Account on the left pane and click Sign in.

          5. Pick an account and double click on it (sign in with your work or school account, if required).

Now, you can try again, and you should not receive the error.

In addition, in my last post I provided the link to Export to Excel from SharePoint, you may try this method, see if this works for you.  

If the issue persists, I’ll suggest you post a new thread in SharePoint server forum for assistance. The reason I ask you to post a new thread is that, this is SharePoint online forum and we have limited resources and very little knowledge about SharePoint server environment and in SharePoint 2013/2016 forum, you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction. We appreciate your understanding.

Regards,

Neha