Extraction of Excel File name automatically in Power BI dash board

[Anonymous]

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

[mussaenda]

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?

[Anonymous]

@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

[Anonymous]

Hi, Thanks for the help. I just copy this code and change the source folder link and created same excel file using same name (LOG_2023081616900_NYK01_CBS350) with same table inside. but I got this error

[Anonymous]

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.

[foodd]

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"

[Anonymous]

No, I want this column of file name in excel sheet where I have other data. like here I want a column of file name please see attached file

[foodd]

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] ...

[foodd]

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? 

[Anonymous]

1. I am taking the file from local folder.

2. In the screen shot I have marked that when we choose source data folder it taked file names automatically by adding new column and what I want is same column with file name in my excel data sheet.

[foodd]

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?