Rearranging Data based on dates

Hi. This one caused some head pain!

Try this:

Formula:

Please Login or Register  to view this content.

Sub-formula B could also be tidied a bit:

B,BYROW(DROP(A,,2),LAMBDA(z,MAX(z)))

so there's only ONE range (subformula A) to adjust.

In Power Query you can do it pretty easily like this

PHP Code: 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    types = Table.TransformColumnTypes(Source,{{"Month", type date}}),
    lstPlan = Table.AddColumn(types, "lPlan", each {1..[Plan]}),
    lstActual = Table.AddColumn(lstPlan, "lActual", each  {1..[Actual]}),
    expPlan = Table.ExpandListColumn(lstActual, "lPlan"),
    expActual = Table.ExpandListColumn(expPlan, "lActual"),
    repNulls = Table.ReplaceValue(expActual,"a","b",(x,y,z)=>  x ?? 0 ,{"lPlan", "lActual"}),
    Plan = Table.AddColumn(repNulls, "Plan_", each if [lPlan] < [lActual]  then "No Plan" else [Month]),
    Actual = Table.AddColumn(Plan, "Actual_", each if [lPlan] > [lActual]  then "No Actual" else [Month]),
    result = Table.RemoveColumns(Actual,{"Month", "Plan", "Actual", "lPlan", "lActual"})
in
    result 


Try it now.

Ok try this, even shorter

PHP Code: 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    types = Table.TransformColumnTypes(Source,{{"Month", type date}}),
    lstActual = Table.AddColumn(types, "tList", each  {1..List.Max({[Actual],[Plan]})}),
    expList = Table.ExpandListColumn(lstActual, "tList"),
    Plan = Table.AddColumn(expList , "Plan_", each if [Plan] < [tList]  then "No Plan" else [Month]),
    Actual = Table.AddColumn(Plan, "Actual_", each if [Actual] < [tList]  then "No Actual" else [Month]),
    result = Table.RemoveColumns(Actual,{"Month", "Plan", "Actual", "tList"})
in
    result