Forum Discussion
Need help with my first custom function
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?
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.
9 Replies
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
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
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
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
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
