anevard
Posts: 109
Joined: 6/5/2006 Status: offline
|
Here is the M-Code for the drinking window. Any suggestions to improve upon it would be welcome.
#"Added CleanVintage" = Table.AddColumn(#"Expanded LastConsumed", "CleanVintage", each
if Value.Is(Value.FromText([Vintage]),Int64.Type) then [Vintage] else 0),
#"Added CleanBegin" = Table.AddColumn(#"Added CleanVintage", "CleanBegin", each
if Value.Is(Value.FromText([Begin]),type text) then [CleanVintage]
else if [Begin] = "" then [CleanVintage]
else if Value.FromText([Begin]) > 3000 then [CleanVintage]
else [Begin]),
#"Added CleanEnd" = Table.AddColumn(#"Added CleanBegin", "CleanEnd", each
if Value.Is(Value.FromText([End]),type text) then CurrentYear
else if [End] = "" then CurrentYear
else if Value.FromText([End]) > 3000 then CurrentYear
else [End]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added CleanEnd",{{"CleanVintage", Int64.Type}, {"CleanBegin", Int64.Type}, {"CleanEnd", Int64.Type}}),
#"Added PrelimDrinWin" = Table.AddColumn(#"Changed Type1", "PrelimDrinkWin", each
if CurrentYear <= [CleanEnd] then (CurrentYear-[CleanBegin]) / ([CleanEnd]-[CleanBegin] +1)
else ((CurrentYear-[CleanEnd]) / ([CleanEnd]-[CleanBegin] +1)+1)),
#"Added DrinkWindow" = Table.AddColumn(#"Added PrelimDrinWin", "DrinkWindow", each
if [PrelimDrinkWin] > 2 then 2
else if [PrelimDrinkWin] < -1 then -1
else [PrelimDrinkWin]),
#"Added DrinkWinRange" = Table.AddColumn(#"Added DrinkWindow", "DrinkWinRange", each
if [DrinkWindow] >= 1.25 then "Dead"
else if [DrinkWindow] >= 1.10 and [DrinkWindow] < 1.25 then "Over"
else if [DrinkWindow] >= .90 and [DrinkWindow] < 1.10 then "Decline"
else if [DrinkWindow] >= .25 and [DrinkWindow] < .90 then "Drink"
else if [DrinkWindow] >= 0 and [DrinkWindow] < .25 then "Young"
else if [DrinkWindow] < 0 then "Wait"
else null),
< Message edited by anevard -- 3/5/2023 6:04:48 AM >
_____________________________
Andrew
|