Power Query Excel Custom Function

Question

Dear All,

I am using the Power Query script shown below, which is sourced from the Excel file highlighted in yellow:

The current setup is working perfectly, but I would like to add a custom column that extracts the date from the source file name above in yellow.

For example, based on the filename, the output should be:

• Details_April_2026.xlsx → April-2026
• Details_May_2026.xlsx → May-2026

I think a custom function is the way to go, but I’m not sure how to implement it.

Any advice?

Thanks

Answer 1

Thanks for the suggestion.

I have the current script below in the Advanced Editor, but I'm not sure where to insert your code snippet.

Could you please guide me on where to place it?

Thanks for your help!

let
    Source = Excel.Workbook(File.Contents("C:\SERVICE BU\05 - Tool\Details_April_2026.xlsx"), null, true),
    #"Export Worksheet_Sheet" = Source{[Item="Export Worksheet",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Export Worksheet_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"COUNTRY", type text}, {"COUNTRY_ISOCODE", type text}, {"PARTNUMBER", type text}})
in
    #"Changed Type"

Answer 2

Hi Luca Tramontana,

You don’t need Invoke Custom Function for this if the file path is hardcoded in the query. The simplest approach is to store the path in a variable, extract the month/year from the file name, and then add it as a custom column.

let
    // Hardcoded path (note the escaped backslashes)
    FilePath = "C:\\SERVICE BU\\05 - Tool\\Details_April_2026.xlsx",
    // Get file name without extension: Details_April_2026
    FileName = Text.BeforeDelimiter(
        List.Last(Text.Split(FilePath, "\\")),
        "."
    ),
    // Split by "_" and build Month-Year: April-2026
    Parts = Text.Split(FileName, "_"),
    MonthYear = Parts{1} & "-" & Parts{2},
    Source = Excel.Workbook(File.Contents(FilePath), null, true),
    #"Export Worksheet_Sheet" = Source{[Item="Export Worksheet", Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Export Worksheet_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"COUNTRY", type text}, {"COUNTRY_ISOCODE", type text}}),
    // Add the Month-Year

With this pattern:

• Details_April_2026.xlsx > April-2026
• Details_May_2026.xlsx > May-2026

So a custom function isn’t necessary unless you want to reuse the same parsing logic across multiple queries. If you later switch to a Folder source, then parsing from the [Name] (or creating a reusable function) becomes the better scalable approach.

Please let me know if this proves useful to you, or if you would like further assistance.

I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment”.   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Use a custom column in Power Query that parses the file path (or file name) and returns the Month‑Year text.

Assuming the Source step is like:

Source = Excel.Workbook(File.Contents("C:\SERVICE BU\05 - Tool\Details_April_2026.xlsx"), null, true),

add a step after Source (or after your last existing step) in the Advanced Editor:

// Get just the file name from the full path
FileName = Text.AfterDelimiter(File.Contents("C:\SERVICE BU\05 - Tool\Details_April_2026.xlsx")[Content.Source], "\\", {0, RelativePosition.FromEnd}),

// Or, if you already have the path as text, use:
// FileName = Text.AfterDelimiter("C:\SERVICE BU\05 - Tool\Details_April_2026.xlsx", "\\", {0, RelativePosition.FromEnd}),

// Extract the month and year between the underscores and before .xlsx
MonthYearText =
    let
        parts = Text.Split(Text.BeforeDelimiter(FileName, ".xlsx"), "_"),
        month = parts{1},
        year  = parts{2}
    in
        month & "-" & year,

// Add as a custom column to your table
AddMonthYear = Table.AddColumn(#"Changed Type", "File MonthYear", each MonthYearText, type text)

If the query already returns a table (for example #"Changed Type"), the only step needed is the last one, where MonthYearText is computed from the file name and added via Table.AddColumn.

Alternatively, the same logic can be wrapped as a reusable custom function and invoked as a column, following the documented pattern:

1. Create a Blank Query and in Advanced Editor define a function that takes a file name and returns month & "-" & year.
2. In the main query, use Add Column > Invoke Custom Function, choose that function, and pass the column that contains the file name as the parameter.

This uses the standard Power Query pattern for creating and invoking custom functions to add a calculated column.

---

References:

• Using custom functions
• Create a custom function from a table reference tutorial
• Create and invoke a custom function
• Add a custom column (Power Query)