Need help with my first custom function

[klehar]

Hi,

I have created a custom function

I have started with just one column in the custom function but I will need many more columns related to a date input that I provide to the custom function

let DateTable = (DateInput as date) as table=>
   
let  
    // Extract Fiscal Year Quarter from Start Date
    
    #"Inserted Start Date Year" = Table.AddColumn( "Year.1", each Date.Year([DateInput]),Int64.Type)
in 
    #"Inserted Start Date Year"
in
    DateTable

But when I try to invoke this I get the following error

What's wrong?

[watkinnc]

Here's how you can fix your original query:

(DateInput as date) as date =>

let

Source = Table.FromValue(DateInput),

    // Extract Fiscal Year Quarter from Start Date

    #"Inserted Start Date Year" = Table.AddColumn(Source, "Year.1", each Date.Year([DateInput]),Int64.Type)

in

    #"Inserted Start Date Year"

--Nate

[watkinnc]

It should be Table.AddColumn(#"Name of your Table", "Year.1", each etc

--Nate

[mahoneypat]

There are a few things potentially wrong.  Along with the missing #, is Year.1 the name of an existing query?  Also, you are using your input parameter, which is a Date (not a table) inside [ ] which will make it look for a column named that.  What are you trying to accomplish with your function (input and desired output)?

Pat

[klehar]

Hi Pat,

My Input will be a date.

Output will be a lot of columns like

Month of date

Day of date

Year of date

etc

[mahoneypat]

This is an example of a function like you describe.  I am curious how you plan to use it, as there may be a simpler way to accomplish your goal.

(DateInput as date) =>
let
Source = DateInput,
#"Converted to Table" = #table(1, {{Source}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type)
in
#"Inserted Year"

Pat

[klehar]

Thanks Pat,

Can you explain me what this line does:

#"Converted to Table" = #table(1, {{Source}}),

And lets say before I roll this function out, I want to modify this so as to add more columns, in that case what modifications do I need to make?

[v-jingzhang · Accepted solution]

Hi @klehar 

This step is to convert the original date value into a table. For table functions (like Table.RenameColumns, Table.TransformColumnTypes, Table.AddColumn...), the first argument should always be a table rather than other types. 

You could modify Pat's code into below and provide a specific date for Source step. In this way, it returns a table. You can add columns to it. After you have added all columns, remove "//" in the code and remove the Source=#date... step. Then it will be converted to a custom function again. 

// (DateInput as date) =>
let
// Source = DateInput,
Source = #date(2021,1,1),
#"Converted to Table" = #table(1, {{Source}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type)
in
#"Inserted Year"

You may find below articles helpful. 

Create Calendar Table Using Power Query M Language (mssqltips.com)

Build a Reusable Calendar Table with Power Query - Excelerator BI

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[klehar]

Hi Pat,

I plan to use this for all my transformations.

Our fiscal year starts from Novemeber.

I want to extract the Year Month Quart Details based on the fiscal year.

This has to be done for each date column and we have many.

Hence I planned to make a custom function.

Let me know if there is an easier way out

[BA_Pete]

Hi @klehar ,

Try putting a '#' in front of your table name in your Table.AddColumn function, like this:

Table.AddColumn( #"Year.1", each Date.Year([DateInput]),Int64.Type)

For what it's worth, I would strongly recommend naming tables and steps in Power Query with no spaces or special characters e.g. Year1 instead of Year.1, or insertedStartDateYear instead of Inserted Start Date Year etc.

This makes it significantly easier to type out manual M code.

Pete