Sharepoint folder data source troubles

[van_r]

I'm having a few troubles in Power BI I was hoping someone could help.

Background: I have a PBI dashboard which uses multiple data sets--all coming from CSV files on my local drive. These data sets are all different and I have them interacting just the way I want them to. If I want to give this dashboard to someone else, I have it currently set up as a template whereby the new user will choose which folder on their local drive contains all their CSV files they want to import to the dashboard. The folder location is set up as a parameter. This parameter feeds the source of each data set. So while I may have my files in C:\Users\UserName\OneDrive\CSV Files, another person could put their files in C:\Users\UserName\Desktop and once they pasted the folder path in the parameter, all the files are located for each of the data sets. It has been tested and works.

Now the challange is: what if people house their CSV files in a sharepoint folder. I've read through the guide on how to use a sharepoint folder as a source, but having trouble setting it up as a parameter. That's issue number 1.

Issue number 2 has to do with sharepoint folder subfolders. So I can get PBI to open up the sharepoint site, which provides a list of all the files in the sharepoint. However, the CSVs I referenced above will likely be in a subfolder not in the root folder of the sharepoint side. I'm getting errors when I try to point PBI to the subfolder.

TLDR: how to use sharepoint folder location as a parameter, and how to point PBI to a sharepoint subfolder directly.

Thanks in advance

[negi007]

@van_r yes you can use a parameter as well to pass the folder path like below. below I have used a parameter Folder_path to pass the value to filter sharepoint folder. Please note that by default initially when you connect to sharepoint folder it will show the list of all files in that sharepoint folder. You will have to define a criteria to filters files. In this example I have used StartWith filter option to filter files based on the name of sub-folder

let
Source = SharePoint.Files("https://xxxxxxxxxxxxxxxxxxxxx", [ApiVersion = 15]),
#"Filtered rows" = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "https://xxxxxxxxxxxxxxxxxxxxxxxx/")),
#"Filtered Rows" = Table.SelectRows(#"Filtered rows", each Text.StartsWith([Folder Path], Folder_Path))
in
#"Filtered Rows"

[negi007]

@van_r  so you wish to have your csv files from a sharepoint folder. So there is process to connect to a shrepoint folder and then using filter option you can filter a subfolder basis matching criteria.

1. When your source is a sharepoint folder, you have to initial connect to a root folder and then it will show the list of all files.

2. Using filter option, you can filter only matching files matching your subfolder name.

3. Then you will list of files based on the matching criteria

4. then you can combined these files

pl. go through the below link for further help  

Power Query SharePoint folder connector - Power Query | Microsoft Docs

Power BI. How to connect up to a sharepoint folder containing multiple files using a dataflow – Debb...

Loading Excel Files from Sharepoint - (powerbi.tips)

[van_r]

thank you--possible to have this as a parameters so i dont have to do this for each CSV I want to load? (currently all CSVs are in 1 folder on local drive and the query looks for a parameters to know where to look--the parameter = folder path)