Reply
Kaycee
Advocate I
Advocate I
Partially syndicated - Outbound

Import Excel/CSV from SharePoint with Metadata Filter

Hi all, 

 

I have documents stored in SharePoint but rather than using folders, I use metadata (columns) to tag files.  The SharePoint Folders Import doesn't include metadata columns but do include file content.  The SharePoint Lists, Odata etc don't appear to include file content but do have metadata.

 

Is there an option where you can retrieve the metadata (aka. custom columns created in SharePoint), filter using those columns and then extract the content from the files?

Thanks
Kaycee

1 ACCEPTED SOLUTION
Kaycee
Advocate I
Advocate I

Syndicated - Outbound

I was fortunate enough to be given this advice in another forum which is effective & efficient:

Step 1
Create a helper query MetaFileNameList that generates a list of file names based on the metadata

Step 2
Use that list to filter your query with SharePoint.Files or SharePoint.Contents, something like:

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

View solution in original post

6 REPLIES 6
Kaycee
Advocate I
Advocate I

Syndicated - Outbound

I was fortunate enough to be given this advice in another forum which is effective & efficient:

Step 1
Create a helper query MetaFileNameList that generates a list of file names based on the metadata

Step 2
Use that list to filter your query with SharePoint.Files or SharePoint.Contents, something like:

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

MFelix
Super User
Super User

Syndicated - Outbound

Hi @Kaycee 

 

For this you need to connect to ODATA for you Sharepoint site should be in:

 

https://<yoursharepointsite>/_vti_bin/listdata.svc

 

Then you just need to select the documents folder:

 

MFelix_0-1640017062443.png

 

MFelix_1-1640017107835.png

The Test Column is one that I created on my sharepoint list.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Syndicated - Outbound

Thanks @MFelix for taking the time to reply. 

 

I can get to this point ok and navigate to the files - but then once I have used the metadata to filter to the files I need, how do I then import the data (contents) of that file? 

 

Thanks!

Syndicated - Outbound

Hi @Kaycee,

 

Then you need to expand the binary colum  that open the files


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Syndicated - Outbound

Unfortunately [Binary] isn't an option.

The only 'expandable' columns that come up are [CreatedBy], [ModifiedBy] and [CheckedOutTo]. There are a heap of other columns but all just contain straight content (such as [Version], [Path], [ApprovedStatus], [ContentTypeID].

Syndicated - Outbound

Hi @Kaycee ,

 

You are correct sorry, in this case you can do a merge between the get data from OData,Feed and the Sharepoint list.

 

This link has to be done using the file name (that I assume are different) be sure to select only the  lines that are present on the ODataFeed. then you can Expand the Binary:

MFelix_0-1640081268382.png

Has you can see the test column is here and the binary also. If you need further assistance like a step by step please tell me.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)