Create a custom function has error

[klehar]

I want to create a custom function for a repetitive task that converts a date column to respective Fiscal Year and Quarter

However, when I invoke this function on a dataset that has a date column I get the following error

I'm not an expert at M code so wondering where am I going wrong.

Code below

(DateColumn as date) =>
let
    Column1 = DateColumn,
    #"Inserted Month" = Table.AddColumn(#"Column1", "Month", each Date.Month([Column1]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Column1]), Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Year", "Fiscal Quarter", each if [Month] = 11 then "Q1" else if [Month] = 12 then "Q1" else if [Month] = 1 then "Q1" else if [Month] = 2 then "Q2" else if [Month] = 3 then "Q2" else if [Month] = 4 then "Q2" else if [Month] = 5 then "Q3" else if [Month] = 6 then "Q3" else if [Month] = 7 then "Q3" else if [Month] = 8 then "Q4" else if [Month] = 9 then "Q4" else if [Month] = 10 then "Q4" else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Ficsal Year", each if [Month] >= 11 then [Year]+1 else [Year]),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column1", "FYQ", each Text.Combine({Text.From([Ficsal Year], "en-US"), [Fiscal Quarter]}, ""), type text),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Merged Column", {{"Ficsal Year", type text}}, "en-US"),{"Ficsal Year", "Fiscal Quarter"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FYQ 2"),
    #"Extracted Last Characters" = Table.TransformColumns(#"Merged Columns", {{"FYQ", each Text.End(_, 4), type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Extracted Last Characters", {{"FYQ", each "FY" & _, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Added Prefix",{{"Column1", "Date"}})
in
    #"Renamed Columns"

[Anonymous]

Hi @klehar 

Can you provide specific operations related to error reporting ? If possible, reflect it in the form of pictures.

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[klehar]

Sure Please find below

let
    Source = List.Distinct(List.Transform(List.Dates(#date(2017, 11, 1), 2500, #duration(1, 0, 0, 0)), each Date.StartOfMonth(_))),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Column1]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Column1]), Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Year", "Fiscal Quarter", each if [Month] = 11 then "Q1" else if [Month] = 12 then "Q1" else if [Month] = 1 then "Q1" else if [Month] = 2 then "Q2" else if [Month] = 3 then "Q2" else if [Month] = 4 then "Q2" else if [Month] = 5 then "Q3" else if [Month] = 6 then "Q3" else if [Month] = 7 then "Q3" else if [Month] = 8 then "Q4" else if [Month] = 9 then "Q4" else if [Month] = 10 then "Q4" else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Ficsal Year", each if [Month] >= 11 then [Year]+1 else [Year]),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column1", "FYQ", each Text.Combine({Text.From([Ficsal Year], "en-US"), [Fiscal Quarter]}, ""), type text),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Merged Column", {{"Ficsal Year", type text}}, "en-US"),{"Ficsal Year", "Fiscal Quarter"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FYQ 2"),
    #"Extracted Last Characters" = Table.TransformColumns(#"Merged Columns", {{"FYQ", each Text.End(_, 4), type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Extracted Last Characters", {{"FYQ", each "FY" & _, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Added Prefix",{{"Column1", "Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Month", "Year", "FYQ 2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"FYQ", "temp"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns1", "date", each #"DIM Date ("([Date]))
in
    #"Invoked Custom Function"

Custom Function below:

(DateColumn as date) =>
let
    Column1 = DateColumn,
    #"Inserted Month" = Table.AddColumn(#"Column1", "Month", each Date.Month([Column1]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Column1]), Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Year", "Fiscal Quarter", each if [Month] = 11 then "Q1" else if [Month] = 12 then "Q1" else if [Month] = 1 then "Q1" else if [Month] = 2 then "Q2" else if [Month] = 3 then "Q2" else if [Month] = 4 then "Q2" else if [Month] = 5 then "Q3" else if [Month] = 6 then "Q3" else if [Month] = 7 then "Q3" else if [Month] = 8 then "Q4" else if [Month] = 9 then "Q4" else if [Month] = 10 then "Q4" else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Ficsal Year", each if [Month] >= 11 then [Year]+1 else [Year]),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column1", "FYQ", each Text.Combine({Text.From([Ficsal Year], "en-US"), [Fiscal Quarter]}, ""), type text),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Merged Column", {{"Ficsal Year", type text}}, "en-US"),{"Ficsal Year", "Fiscal Quarter"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FYQ 2"),
    #"Extracted Last Characters" = Table.TransformColumns(#"Merged Columns", {{"FYQ", each Text.End(_, 4), type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Extracted Last Characters", {{"FYQ", each "FY" & _, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Added Prefix",{{"Column1", "Date"}})
in
    #"Renamed Columns"

[Anonymous]

Hi @klehar 

There may be a problem with your time column format, I tested your M code, everything is displayed normally.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACGlWB0Y1wiVa4zKNUHlmqJyzVC55qhcC1SuJQrX0ACVi+oqQ4irYgE=", BinaryEncoding.Base64), Compression.Deflate)), 
    let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Column1]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Column1]), Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Year", "Fiscal Quarter", each if [Month] = 11 then "Q1" else if [Month] = 12 then "Q1" else if [Month] = 1 then "Q1" 
    else if [Month] = 2 then "Q2" else if [Month] = 3 then "Q2" else if [Month] = 4 then "Q2" else if [Month] = 5 then "Q3" else if [Month] = 6 then "Q3" else if [Month] = 7 then "Q3" 
    else if [Month] = 8 then "Q4" else if [Month] = 9 then "Q4" else if [Month] = 10 then "Q4" else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Ficsal Year", each if [Month] >= 11 then [Year]+1 else [Year]),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column1", "FYQ", each Text.Combine({Text.From([Ficsal Year], "en-US"), [Fiscal Quarter]}, ""), type text),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Merged Column", {{"Ficsal Year", type text}}, "en-US"),{"Ficsal Year", "Fiscal Quarter"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"FYQ 2"),
    #"Extracted Last Characters" = Table.TransformColumns(#"Merged Columns", {{"FYQ", each Text.End(_, 4), type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Extracted Last Characters", {{"FYQ", each "FY" & _, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Added Prefix",{{"Column1", "Date"}})
in
    #"Renamed Columns"

I have attached my pbix file , you can refer to my M code .

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[klehar]

HI Alisa,

I want to use that table as an m code custom function. 

That is the place where I get the error