Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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.
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
It should be Table.AddColumn(#"Name of your Table", "Year.1", each etc
--Nate
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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
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
Proud to be a Datanaut!