How to split files sourced from an folder

[van_r]

I have a dashboard that uses about 10 different CSVs as data sources. Each CSV is a different type of report--meaning, these are not different versions of the same report. Currently, I've loaded each CSV into PBI separately and in Power Query Editor, each CSV shows up under "Other Queries" and this ultimately feeds the data tables used in the model.

This dashboard will be used by multiple different project teams. Each team will have their own data reports to load into the dashboard--so they will obtain their own 10 CSV reports that will need to get loaded into the dashboard for analysis.

The way the data is being pulled into PBI currently, if I give the dashboard to a project team, in order for them to have PBI Desktop find their CSV files, they need to go into each query for each of the 10 reports and change the "source" of the query to the CSV on their desktops. To avoid having to manually change the source for all 10 queries (or more in the future), I'd like to have the teams be able to drop all CSVs into a single folder on their desktop, then go into Query Editor and just point a data source to the folder instead of each individual CSV.

When I Get Data in Power BI and i choose "folder" instead of a file type, PBI correctly loads all data from the files in the selected folder, however, each file isn't included in the data model as a separate table. Instead, the data table created just has the listing of the files loaded--I need help figuring out how to tell PBI to "split out" each file into its own table for use in the dashboard. Below is a screenshot of the table created (screenshot from the data model) showing what gets pulled in. Thanks in advance.

[v-kelly-msft · Accepted solution]

Hi  @van_r ,

When you connect to a folder and get a window as below:

Click Edit,go to query editor and select the file you need by filtering the name column.

Finally get the content of the file by expanding "Content" column:

For details,you could check below:

https://powerbi.tips/2016/06/loading-data-from-folder/

Duplicate table and repeat the above steps to get all the needed files.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[parry2k]

@van_r does each csv has the same structure? Even though you need 10 tables, but if each csv is the same structure, then keep the file name as part of the dataset and have one table, and the user can use filename as an identifier to filter the data for the specific file.

[van_r]

No totally different file structures

[parry2k]

@van_r it is not at all clear what you are trying to achieve? Seems like I'm totally missing it. Can you try to explain it a little further with some examples?

[van_r]

Original post revised--does this clarify?