Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kaycee
Advocate I
Advocate I

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

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

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

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



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!

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



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].

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



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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