Excel filename as column

[twister8889]

Hi Guys,

How can do I get the filename, split, and use it into a new column? I would like to have a column with 1 first string: Custom with content  =ETL and Custom2 = excel

[v-yingjl]

Hi @twister8889 ,

If you connect to the file directly, I'm afraid you cannot get the file name directly in power query.

You can try to connect to folder and try this query:

let
    folder = "C:\Users\xxx\xxx\excel folder",
    Source = Folder.Files(folder),
    RemoveUnused = Table.SelectColumns(Source,{"Content","Name"}),
    Addtables = Table.AddColumn(
        RemoveUnused,
        "Custom",
        each Table.PromoteHeaders(Excel.Workbook([Content]){[Item = "Sheet1",Kind = "Sheet"]}[Data])
    ),
    Addtables2 = Table.AddColumn(
        Addtables,
        "Custom2",
        each Table.PromoteHeaders(Excel.Workbook([Content]){[Item = "Sheet2",Kind = "Sheet"]}[Data])
),
    #"Removed Columns" = Table.RemoveColumns(Addtables2,{"Content"})
in
    #"Removed Columns"

You will get a File name column and two table columns under this query.

Now you can split the Name column twice one by space and another by . , remove unnecessary columns and rename the new column:

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[twister8889]

First of all, thank you for your answers and I'm sorry for the delay.

When the scenario is to get one file and sheets with the same structure, these options to solve the problem. I'm new in power bi and in my project, so I noticed that I need to get data of multiples files (excel) with different sheets structures. However, I will be open another thread to this scenario, for this I will mark the first answer as correct, but all solved my problem.

Thank you so much, guys.

[Greg_Deckler]

@twister8889 - Had another thought on this. Make the file name/path a query parameter. Then you can set it and use it to create a new column in your query.

[amitchandak · Accepted solution]

@twister8889 , not very clear , refer

https://techcommunity.microsoft.com/t5/excel/power-query-get-file-name/td-p/1429886

https://www.sqlservercentral.com/blogs/include-file-name-in-content-using-power-query

[Greg_Deckler]

@twister8889 - Maybe check out a Folder query