Forum Discussion
Extraction of Excel File name automatically in Power BI dash board
Dear all,
I want my BI dashboard to extract excel file name automatically for that may be I need to create a column and use a code to get the name. so please any one can guide me or tell me which code i need to use. So, every time when I change my source data file the dash board must extract the file name and disply it.
Thank you
10 Replies
What is the source of (where are they stored) the Excel Files?
Would this be SharePoint Online, OneDrive, Network Share, local folder, etc?
The powerquery-m is straightforward, and the process is maintainable. Do you have a working Powerbi report
currently, and would you share it? Also, if you would share what the desired results
for the report would look like?
So, you have the File Name already. Good.
What you need is the worksheet name or names from the workbook.
Are the Excel Workbooks the same structure (count of sheets) from day to day?
If I understand, you would like to understand how to add the file
name to the Query named 'SMB', and have it appear in position 1
to the left of [EventID] like it appears in Query 'experiment'?[Excel workbook Filename], [EventID], [Severity] ...
I dont know you get it right or not. but my simple requirment is I want my excel file name in to an column by M code or any other mean. It is happens automatically when we choose folder as source data (it creates automatically a clumn with "Name" and show all excel file names inside that folder) but when we choose excel file as source data it never happens. so I want a cloumn in my excel file who have the name of that excel file.
Note: Assumes that you are importing log data saved in Excel format from a local folder.
This is one way, and there are other variants.
Source Path is a local folder = C:\Users\user\Desktop\SwitchLogs
Excel File represents a log file with four columns :
EventID, Severity, Date, Duration - The first two lines of the m-code will be the folder source using
= Folder.Contents("C:\Users\user\Desktop\SwitchLogs") - Add a custom column that retrieves the [Content] column from each Excel file.
= Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])) - Remove Other Columns leaving only [Name] and [Custom] columns
= Table.SelectColumns(#"Added Custom",{"Name", "Custom"}) Expand [Custom] column, and choose 'Data' from the list
= Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Data"}, {"Data"})
- Expand [Data] column
= Table.ExpandTableColumn(#"Expanded Custom", "Data", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}) - Promote Headers using the first row as the header row
- Rename the first column containing the Excel Workbook name to [File Name]
- Close and Load
Results:
Query:
let Source = Folder.Contents("C:\Users\user\Desktop\SwitchLogs"), #"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Data"}, {"Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Expanded Custom", "Data", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}), #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LOG_2023081616900_NYK01_CBS350.xlsx", type text}, {"EventID", Int64.Type}, {"Severity", Int64.Type}, {"Date", type date}, {"Duration", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"LOG_2023081616900_NYK01_CBS350.xlsx", "File Name"}}) in #"Renamed Columns"- The first two lines of the m-code will be the folder source using
foodd I am facing one little problem if you can help then please help.
After doing 2nd last step as you mentioned above (Rename the first column containing the Excel Workbook name to [File Name]) when I choose a different file from name column the format will disturbe again like for new file headers will appear again in first row while the old headers are still there.
If you have any solution please let me know. Thank you
Hi Anonymous ,
Filename is automatically extracted if you are using the folder as your source.
It is usually called Source.Name or Name.
Is it not showing on your side? Don't you want to use the folder as soure instead of the excel so you can achieve your requirement?
