Forum Discussion
extract excel file name
Please is it possible to extract the file name of an excel file and append it as a column ? Meaning the column should be polpulated with only one value in all its cells which is the file name.
Thanks in advance
Hi Anonymous
You don't need to create a folder and move the file. You can connect to its current folder. After connection, you will see all items in this folder, click Transform Data rather than Combine. Then you will enter Power Query Editor. Here you can filter the rows to only keep the files you want to use in the report. For example, click down arrow on any column header to filter the rows.
It doesn't matter how many files are in a folder because you can always filter them to only keep the ones you need. Note that you should filter files first, then combine the contents.
Later, you can click Refresh in Power BI Desktop to bring in the latest data of these Excel files. If published to Power BI Service, you can set up a scheduled refresh to refresh data.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
4 Replies
Anonymous , refer if these can help
https://www.goodly.co.in/dynamic-file-path-power-query/
https://community.powerbi.com/t5/Desktop/Excel-filename-as-column/td-p/1343909
Hi Anonymous
A simple way is to use the Folder connector instead of the Excel connector. You can store the Excel file in a folder, connect to this folder via Get data > More... > Folder. After connecting to the folder, combine files (you only have one file here). After that, you will find the file name populates a column Source.Name automatically.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.Hi v-jingzhang this method is good if the dashboard belongs to me, but I work in a corpperate environment and I'm not permited to change the location of the file. Also, the reports get updated after every two weeks so even if I have to, I'll have to creat a new folder every two weeks.
And also there is not only one file in the folder where the file is being stored (There are about 100 files and still counting)Hi Anonymous
You don't need to create a folder and move the file. You can connect to its current folder. After connection, you will see all items in this folder, click Transform Data rather than Combine. Then you will enter Power Query Editor. Here you can filter the rows to only keep the files you want to use in the report. For example, click down arrow on any column header to filter the rows.
It doesn't matter how many files are in a folder because you can always filter them to only keep the ones you need. Note that you should filter files first, then combine the contents.
Later, you can click Refresh in Power BI Desktop to bring in the latest data of these Excel files. If published to Power BI Service, you can set up a scheduled refresh to refresh data.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
