Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 c...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    foodd
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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"