I need Help With A Date Formula

First of all, thank you dufoq3 for being patient with me, but now this error shows up, but I am 100% certain that I have that column name

Expression.Error: The column 'Est Close Date' of the table wasn't found.

Details:

    Est Close Date

This is the whole code I enterd is it corect? or did I do somthing wrong

let

    Source = opportunities_table,

    TrimmedColumnNames = Table.TransformColumnNames(Source, Text.Trim),

    ChangedType = Table.TransformColumnTypes(TrimmedColumnNames,{{"Est Close Date", type date}, {"Close Date", type date}}),

    Ad_EstCloseYear = Table.AddColumn(ChangedType, "Est Close Year", each Date.Year([Est Close Date]), Int64.Type),

    Ad_CloseYear = Table.AddColumn(Ad_EstCloseYear, "Close Year", each Date.Year([Close Date]), Int64.Type),

    Ad_NewCloseDate = Table.AddColumn(Ad_CloseYear, "New Close Date", each

        [ today = Date.From(DateTime.FixedLocalNow()),

          thisYear = Date.Year(today),

          result = if [Deal Stage] <> "Won" then null else

                   if [Close Date] = null and [Est Close Year] = thisYear then today else

                   if [Close Date] = null and [Est Close Year] < thisYear then [Est Close Date] else

                   if [Close Year] > [Est Close Year] then [Est Close Date]

                   else [Close Date]

        ][result], type date)

in

    Ad_NewCloseDate

Hi, double check that column name: double click to column header and check if the name is exactly: "Est Close Date" - if not, rename it. You have to perform rename before ChangedType step - so select Source step and then double click on the column name you want to rename.

Hello, I tied using the original name of the columns and it did solve the error the [Est Close Date] was [estimatedclosedate] and the [Close Date] was [actualclosedate] when I changed the names in the code it works with no error but there is one problem the values of the new column are all "Table" I thoght it might be okay but when I tested it in the dashboard all the values of the new column are "Table" 

I can only work with sample data you provided (which does not match with reality). It would be good to share few rows of real data (i.e. via google drive with link public permissions). Remove sensitive data if necessary.

I tried the code with the sample data and it also gave me an 

Expression.Error: The column 'Value' of the table wasn't found.
Details:
Value

and as you know the "value" column is there and then I deleted the value as you said before and still the same erorr but with "Est Close Date" and this is using the sample data I provided this is my code 

let
Source = Excel.Workbook(File.Contents("C:\Users\Mohammed\Downloads\Test Date.xlsx"), null, true),
TrimmedColumnNames = Table.TransformColumnNames(Source, Text.Trim),
ChangedType = Table.TransformColumnTypes(TrimmedColumnNames,{
{"Est Close Date", type date}, {"Close Date", type date}}),
Ad_EstCloseYear = Table.AddColumn(ChangedType, "Est Close Year", each Date.Year([Est Close Date]), Int64.Type),
Ad_CloseYear = Table.AddColumn(Ad_EstCloseYear, "Close Year", each Date.Year([Close Date]), Int64.Type),
Ad_NewCloseDate = Table.AddColumn(Ad_CloseYear, "New Close Date", each
[ today = Date.From(DateTime.FixedLocalNow()),
thisYear = Date.Year(today),
result = if [Deal Stage] <> "Won" then null else
if [Close Date] = null and [Est Close Year] = thisYear then today else
if [Close Date] = null and [Est Close Year] < thisYear then [Est Close Date] else
if [Close Year] > [Est Close Year] then [Est Close Date]
else [Close Date]
][result], type date)
in
Ad_NewCloseDate

I'm sorry, but my query definitely works with sample data. I see space in your sample screenshot in Value column. There is probably also space at the end of column name Est Close Date. But I handled extra spaces at the beginning and end of column name with TrimmedColumnNames step - so this shouldn't be an issue.

Sharing your query won't help me. I don't see your data / column names etc. so I can't help you without this.

Here is the sample data with the error 

https://drive.google.com/drive/folders/1tVJbW8RdRg7jgNqVb4BDKpnqQ9RdPAJv?usp=sharing

I need this file...
C:\Users\Mohammed\Downloads\Test Date.xlsx, but what I see from your query is that you didn't read carefully my Note!

Try this, but this time do not add it as custom column. Open Advanced Editor, delete whole query and paste there this one.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Mohammed\Downloads\Test Date.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    TrimmedColumnNames = Table.TransformColumnNames(Table1_Table, Text.Trim),
    ChangedType = Table.TransformColumnTypes(TrimmedColumnNames,{
    {"Est Close Date", type date}, {"Close Date", type date}}),
    Ad_EstCloseYear = Table.AddColumn(ChangedType, "Est Close Year", each Date.Year([Est Close Date]), Int64.Type),
    Ad_CloseYear = Table.AddColumn(Ad_EstCloseYear, "Close Year", each Date.Year([Close Date]), Int64.Type),
    Ad_NewCloseDate = Table.AddColumn(Ad_CloseYear, "New Close Date", each 
        [ today = Date.From(DateTime.FixedLocalNow()),
          thisYear = Date.Year(today),
          result = if [Deal Stage] <> "Won" then null else
                   if [Close Date] = null and [Est Close Year] = thisYear then today else
                   if [Close Date] = null and [Est Close Year] < thisYear then [Est Close Date] else
                   if [Close Year] > [Est Close Year] then [Est Close Date]
                   else [Close Date]
        ][result], type date)
in
    Ad_NewCloseDate

I uploaded the Excel file but I have a question my real data is connected through Daynimc 365 online dose that make a difference?

I've edited my previous post few seconds ago - read it please.

Ok now it works in the sample but the main data still have issues I don't know if it's because the connection is online and I don't have the data in a local folder, I will start a fresh connection and try it again 

You have to prepare your real data to same state as you have you sample in excel. Then just edit Source step of my query and refer your query name with real data (you can check it again in note below, but read carefully)

I don't know much about coding in power query but couldn't we just write a code that fits in a custom column without all that source stuff?

Ok I started a new connection and I loaded it through Dynamic 365 Online and then I clicked transform data and went straight to Advanced Editor and this showed up 

let

    Source = OData.Feed(There is a link here to the CRM Conection, null, [Implementation="2.0"]),

    opportunities_table = Source{[Name="opportunities",Signature="table"]}[Data]

in

    opportunities_table

where can I find my source is it all of it ?

Hello dufoq3, sorry it took me this long to get back to you yesterday I finally got the code working and sorted the problems I had with the source as you can tell I was new to Power Query and didn't know anything about it or how does it work, so the code works perfectly now and I'm sorry but I need one more thing, as you know this code only works with "Won" deals but could you make it that it would work for "Lost" also? the code is below and before you go to it I just wanted to let you know that I changed the "Deal Stage" in the code to the orginal value so in this case the "Deal Stage" is "alfa_roadmapstatus" I thgout it would be beter if I coded from the source not from the custom columns so in this case "Won" = 6 and "Lost" = 8 here is the code 

Source = OData.Feed(My Data Source, null, [Implementation="2.0"]),
opportunities_table = Source{[Name="opportunities",Signature="table"]}[Data],
TrimmedColumnNames = Table.TransformColumnNames(opportunities_table, Text.Trim),
ChangedType = Table.TransformColumnTypes(TrimmedColumnNames, {
{"estimatedclosedate", type date}, {"actualclosedate", type date}
}),
Ad_EstCloseYear = Table.AddColumn(ChangedType, "Est Close Year", each Date.Year([estimatedclosedate]), Int64.Type),
Ad_CloseYear = Table.AddColumn(Ad_EstCloseYear, "Close Year", each Date.Year([actualclosedate]), Int64.Type),
Ad_NewCloseDate = Table.AddColumn(Ad_CloseYear, "New Close Date", each
let
today = Date.From(DateTime.FixedLocalNow())
in
if [alfa_roadmapstatus] <> 6 then null else
if [actualclosedate] = null and [estimatedclosedate] <> null and [estimatedclosedate] > today then today else
if [actualclosedate] = null and [estimatedclosedate] <> null then [estimatedclosedate] else
if [actualclosedate] <> null and [Close Year] > [Est Close Year] then [estimatedclosedate] else
[actualclosedate]
, type date),