Hi,
Im trying to rearrange a table to a different format based on dates. Can use either Excel 365 solution or Power Query
Desired results attached
Hi,
Im trying to rearrange a table to a different format based on dates. Can use either Excel 365 solution or Power Query
Desired results attached
Hi. This one caused some head pain!
Try this:
Formula:Please Login or Register to view this content.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
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
Last edited by JEC.; 03-16-2024 at 08:16 AM.
Hi Glenn, Awesome work, appreciate your effort.
Ran into some error when input values were changed, hope it should be an easy fix
Attached sheet
Hi JEC,
Nice work! I would also like to have a query solution but there seems to be some glitches when the input values are changed
For eg I changed D3 to 5 and E3 to 2 and refreshed the query, the output table was giving total 10 rows instead of 5
Also, no.of plan and actuals were also wrong.
Try it now.
Hi Glenn,
Works perfect now. Thanks a lot
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
Last edited by JEC.; 03-17-2024 at 06:34 AM.
This works, JEC !!
Thanks a lot
Hi Glen/JEC,
Sorry to state additional requirement at this stage but I realized that a modification is required for my data to benefit further analysis.
The requirement is that any "No Actual" cells for an ID should be filled using actuals from the following ID ( if available)
This is to fill out all the Plan before actual for any subsequent IDs is spread out against the next plan
Attaching sheet with desired results
How about this?
Please Login or Register to view this content.
Here the PQ version
Please Login or Register to view this content.
Hi windknife and JEC,
Thanks for your response
The actual should be distributed only if there is a plan ( Attaching sheet with desired results)
In this sheet, plan for Jan is 10, total for actuals for Jan, Feb, Mar is 9 so they are distributed against Jan
For the remaining 1 plan, is taken from next available actual - Apr and the remaining actuals for Apr are distributed against Apr plan
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks