Get data from multiple files in Smartsheet

[jacquelineluna]

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.

[Anonymous]

I didn't find the accepted answer above to work in regards to the Smartsheet connector. Drilling down to files in a directory allows you to expand the table column, so I went back and substituted that step with the folder column from the previous. The resultant view is almost a flat file view allowing you to filter for specific file names, then expand the table.

let
Source = Smartsheet.Tables(),
#"Key1" = Source{[Key="Key1"]}[Data],
#"Key2" = #"Key1"{[Key="Key2"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(#"Key2",{"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Name","Data"}, {"Name","Data"})
in
#"Expanded Data"

[PhilipTreacy · Accepted solution]

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

Resulting in this list

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.

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

[jacquelineluna]

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]

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]

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