We cannot convert the value #date to type Function

[ahmadov_10]

Hello Experts,

in Power Query editor i need to configure a StartDate and EndDate 

I maked this code and the formulas StartDate and EndDate works fine :

let

source ..... 

StartDate = Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.FixedLocalNow())), -13), // 01/06/2021
EndDate = Date.AddMonths(Date.EndOfMonth(DateTime.Date(DateTime.FixedLocalNow())), -1), // 30/06/2022
#"Addedstart" = Table.AddColumn(#"Changed Type", "StartDate", each Date.ToText(StartDate)),
#"Addedend" = Table.AddColumn(#"Addedstart", "EndDate", EndDate)
in
#"Addedend"

the problem in the step Addedstart : no errors but , not showing results in column  : so how to add correctly the value of variable and convert it to date @Vijay_A_Verma    @BA_Pete . thanks a lot

[v-kkf-msft · Accepted solution]

Hi @ahmadov_10 ,

You are missing "each" in this step, and it will throw an error "We cannot convert the value #date to type Function".

#"Addedend" = Table.AddColumn(#"Addedstart", "EndDate", EndDate)

Please try the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSRSk2FgA=", 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 text}}),
    StartDate = Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.FixedLocalNow())), -13), // 01/06/2021
    EndDate = Date.AddMonths(Date.EndOfMonth(DateTime.Date(DateTime.FixedLocalNow())), -1), // 30/06/2022
    #"Addedstart" = Table.AddColumn(#"Changed Type", "StartDate", each Date.ToText(StartDate)),
    #"Addedend" = Table.AddColumn(#"Addedstart", "EndDate", each EndDate)
in
    #"Addedend"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

Hi @ahmadov_10 ,

Why are you trying to apply a variale to a new column? Why not just write the variable calculation into the new column calculation instead?

Pete

[ahmadov_10]

Hello , thanks for answer : what I shared was a test but my business need is to create one column called date

between the variables startDate and EndDate. and i want to use it with many columns in my script Power Query , so it's interesting to use variables but i'm open for any suggestions. 

[BA_Pete]

Ok, it sounds like we need to go back a step then.

Can you describe accurately what data you are starting with, and explain exactly what your end-goal is please?

It sounds like you actually want to do something quite simple, but are trying to go about it in an over-complicated or roundabout way: XY Problem 

Pete