cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jacquelineluna
Frequent Visitor

Get data from multiple files in Smartsheet

I have documents within folders (each folder has a document with the word "Plan" in the title) in Smartsheet. I need to be able to pull all the files (which have the word "Plan") within the folders into PowerBI. Ideally, it needs to be pulled into a single table.  

Can anyone provide some guidence?

Thank you.

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

Hi @jacquelineluna 

Once you have the list of files in PBI you should have a table with a column that shows the files name.  Using the get from Folder connector as an example.

 

Filter this column to only show filenames that contain the word Plan

j1.png

j2.png

 

j3.png

 

Resulting in this list

j4.png

 

With the Get from Folder connector you can then combine these files.  The functionality available to you may be different and you may needto manually start a combination. 

If so can you please show us what you are seeing in PBI after loading the list of files from Smartsheet.

j5.png

 

Once you set up this query all you need to do is refresh it and any new files will be loaded, processed and added to the table.

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
PhilipTreacy
Super User III
Super User III

Hi @jacquelineluna 

Once you have the list of files in PBI you should have a table with a column that shows the files name.  Using the get from Folder connector as an example.

 

Filter this column to only show filenames that contain the word Plan

j1.png

j2.png

 

j3.png

 

Resulting in this list

j4.png

 

With the Get from Folder connector you can then combine these files.  The functionality available to you may be different and you may needto manually start a combination. 

If so can you please show us what you are seeing in PBI after loading the list of files from Smartsheet.

j5.png

 

Once you set up this query all you need to do is refresh it and any new files will be loaded, processed and added to the table.

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

jacquelineluna
Frequent Visitor

Thank you for your help so far.  I already can manually get all the docs from smartsheet into BI.  However, I need to take it one step further and could use some futher clarification on the suggestions:

 

I need to ensure that this process is automated so that any new folders that are put into the main folder (within Smartsheet) will be searched for files with "Plan" and automatically brought in for processing. Again, all the "Plans" need to be automatically (even when new files are added) brought into a single table.

 

What suggestions do you have to automate this process?

 

@v-yingjl 

@PhilipTreacy 

v-yingjl
Community Support
Community Support

Hi @jacquelineluna ,

Not familiar with Smartsheet connector but sounds like filter specific files from folders and combine them into one query.

If so, you can filter each file name which includes 'Plan' word for each folder as a table, then create a blank query to combine them as a new table if they have the same structure, like:

Source = Table.Combine({#"Table A",#"Table B",#"Table C"})

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PhilipTreacy
Super User III
Super User III

Hi @jacquelineluna 

Use the PBI connector to get your files

https://www.smartsheet.com/marketplace/apps/microsoft-power-bi

and in Power Query filter so that it only gets the files you want.  You can then load all the data from each file into a single table.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors