split data source

[Varshi288]

Hi 

I have report live  in power bi. where as  the data source is an excel file from sharepoint folder.Every Month  we use to  get data from 7 regions (US, UK,IND etc..) which are directly uploaded to  sharepoint  by them.we do  some changes and consolidate into one excel file.here issue is excel file is getting  large every month. So planning to  do  in two methods which is better and  feasable.

Method 1.To  split the file into  regions and feed to  power bi.( In initila developnet we know how to combine two  files. but here the live  file need to  split and combine once again.

Method 2 : Is it possible to  add multiple files from multiple folders(region) from sharepoint. Due to security  and privacy each region have access  to their own folder.

Can this be automated .

All above is chage for live reports.

Need help or  any trips and tricks. Thanks  in advance.

[Anonymous · Accepted solution]

Hi Varshi,

I have a similar scenario, i have different application teams which load their data into a specific folder in the sharepoint. These are then loaded into power bi.

Here is my Folder Structure (RootFolder  ->AppFolder1,AppFolder2,AppFolder3 --> 1 excel Under each AppFolder ->aaa_20190525.xlsx)

You can achieve this by combining all the files under RootFolder :

= Table.SelectRows(Source, each Text.Contains([Folder Path], "https://xyz.sharepoint.com/teams/MyTeam//Review Reports/PowerBI - DataSource/")),

1. Connect to Sharpoint
2. Under FolderPath - Filter by RootFolder
3. Combine Files - This step Invoke's custom function
4. (Ensure that you have Source column also expanded along with files)
5. Create a custom column to identify the folder name using the Source Column
6. Now you have all the data which can be differentiated by Folder Name

[Varshi288]

Thank You Nishanth.

[Anonymous]

Hope this worked, can you mark this as the solution and give your feedback with Kudos! This will help others as well