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