Forum Discussion
I need Help With A Date Formula
I didn't use hardcode values 2023 and 2024 because it won't work next year. Instead of that I used thisYear and compared vs thisYear.
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZZLbtwwDIavMph1OhAp6rXvqkBQoF10EWQxfQANAjRBFzmPz+KTlZRtiYwrJ6sxDX2mxP8nNXd358/Pzx+cg/PN+eP15eEn/yLOU8DET+ly8pcTOvQcQODHGhFH357+nO9vNhz5zZen74KUefJZ1mO5nEKjw/apPewbjGmeIMjHeG18V2Z58/X69/pboDhPKZaViI3YQUFBhU+bIK9QHkOxbTPQPFHZEBgjSVWVYJ5yLktVczubCSwtCT5dfzyuJ/M+vi6MCSxcOhxYkljqesfbxY7b0HwAnMrOW09FLMKSprbeBBbWdoI8T1gLjF4TNrI8aoH8PLkopQReXrorTGT5biniyuVauaDNvCOoEcVxuaop4UhcCN0PckKqRNY2l2jgW4iqQpHr6+u2GKBxxtQyRm4UxPgmkfseRQQpK5BpLRpaCIoWMYmHcEnoDT7QEJ3uTE5Pzq2qhfekR+hjgTWELMm5muC6g0xk6T6RAud2tVubHP9zAHbPiJ6htqqdYTuEtIg8R3K1qRh77BvsvsnsbPQ1D2nf2MjSsbclME6Blk48SJiUEGI1qmY33bdjstbeS0Os7Y/jPNovhf3iy9pENMzj1ZRJaetUODyP18Ml8N5KdssgVWZIY2t4fOXrZSqT9rUJLO2tADnDOknVWHZDV/s+ZyLTgBsNMC5Sdwy4re+LtsiOUHcV906pFihHk8L32ULcLyXAqsNBkqx8hbyxXJvsWDt1LRW5RZeGQXN+CQdjk0Tp24fHX9I9nNFtl323sr2SLa2dk+VfTp2Ib4p3/w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Opp-Num" = _t, Name = _t, Value = _t, #"Est Close Date " = _t, #"Close Date" = _t, #"Deal Stage" = _t]),
TrimmedColumnNames = Table.TransformColumnNames(Source, Text.Trim),
ChangedType = Table.TransformColumnTypes(TrimmedColumnNames,{{"Value", type number}, {"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
Wow this is a lot, thank you dufoq3, I am sorry but could you make it like this code below
= Table.AddColumn(#"Added Custom10", "Test Close Date formula", each if [Deal Stage] = "Won" then
if [Close Date] = null or [Close Date] = "" then
if [Est Close Date] = #date(2023) then [Est Close Date]
else if [Est Close Date] = #date(2024) then Date.From(DateTime.LocalNow())
else if [Est Close Date] = null or [Est Close Date] = "" then Date.From(DateTime.LocalNow())
else null
else [Close Date]
else null)
This was from me trying to do it with POE and of course it failed but I don't know how to apply your code
- dufoq32 years agoCommunity Champion
Heve you read note below my post?
- mohammedald2 years agoHelper I
sorry I just saw it, but it seems that I can't get my query is this because I use Dynamics 365 online to connect the dataset?
- mohammedald2 years agoHelper I
I think I figured it out, sorry this is my first time using Power Query so please be patient with me
when I changed the query as you said in the note the code worked but it gave me an error in the new column rows when I clicked the error it said this
Expression.Error: The column 'Value' of the table wasn't found.
Details:
Value
I think this is because the true value name in my data set is not the same the only problem is I have nearly 400 columns in the dataset do I need to identfy them all?