Latest Enterprise DNA Initiatives


Importing SharePoint Files Filtering on Metadata

Hi all,

I’d like to import SharePoint Online files. These files are not sorted into folders but instead have columns created which allows end users to ‘tag’ information about the files. Hence the steps required would be

  1. Import list of ‘Documents’ in a SharePoint page
  2. Filter on a metadata Column
  3. Expand the content in the file.

The problem I am finding is that using SharePoint.Files or SharePoint.Contents will return a list of files but not any metadata.

Using OData.Feed (noting you have to put “/_vti_bin/listdata.svc” on the end of the SharePoint site path when doing this option) or SharePoint.Tables will return metadata but will not return the [Contents] column which will enable you to expand the file data.

Is there any training on how to do this… or I’d appreciate advice if not.

I’d like to avoid using a ‘merge’ if possible as it’s typically a slower transformation in Power Query and a more complex and seemingly unnecessary step to combine two of the above options.

I can’t share a file in this instance because they link to live SharePoint files. I have however screenshot an extract. I’ve highlighted in red squares the Filename and the Metadata columns that I had created. You’ll note there is no [Content] column that allows data to be extracted.

Did you check out the two new courses on sharepoint courses on the learning portal that EDNA just posted?

Personally, I haven’t review the material within the courses but I’m sure there might be something there to help you.

I hope this might help.
Thanks
Keith

Hi @kaycee,

Welcome to the Forum.

You can achieve that without a merge…
Create a helper query MetaFileNameList that generates a list of file names based on the metadata
Use that list to filter your query with SharePoint.Files or SharePoint.Contents, something like:

Table.SelectRows( PrevStepName, each ( List.Contains( MetaFileNameList, [FileNameCol] ) =true ))

I hope this is helpful

1 Like

Apologies for the delayed response @Melissa, but thank you. This was a brilliantly simple solution.

Cheers
Kaycee