How to add Rows based on a percentage increase of the current number of rows

[DMT19]

Hi, 

I have two files, one that contains the historical data and one that contains a % increase/decrease.

What i am hoping to do is add/remove rows based on the % increase/decrease for each age band.

Thanks in advance.

Example:

ID 6100235 had 16 attendances on the 22/08/2022 last year, they have advise that they are expecting a 5% increase in that age for next year.

Calculate 16 (number in the age) *5% = .8 (round up to nearest whole number) and then add the required number of rows to the table with only the Centre ID, Date and Age added the rest of the cells can be blank.

If the number is negative do the same (roundup to nearest whole number) and randomly remove row or rows.

Historical File:

ID	Name	Date	Age	Sign In	Sign Out
6100235		22/08/2022	0 to 2	8:45	15:35
6100235		22/08/2022	0 to 2	7:00	12:00
6100235		22/08/2022	0 to 2	6:30	16:00
6100235		22/08/2022	0 to 2	8:45	15:35
6100235		22/08/2022	0 to 2	7:00	12:00
6100235		22/08/2022	0 to 2	6:30	16:00
6100235		22/08/2022	0 to 2	8:45	15:35
6100235		22/08/2022	0 to 2	7:00	12:00
6100235		22/08/2022	0 to 2	6:30	16:00
6100235		22/08/2022	0 to 2	8:45	15:35
6100235		22/08/2022	0 to 2	7:00	12:00
6100235		22/08/2022	0 to 2	6:30	16:00
6100235		22/08/2022	0 to 2	8:45	15:35
6100235		22/08/2022	0 to 2	7:00	12:00
6100235		22/08/2022	0 to 2	6:30	16:00
6100235		22/08/2022	0 to 2	8:45	15:35
6100235		22/08/2022	2 to 3	8:45	15:35
6100235		22/08/2022	2 to 3	7:00	12:00
6100235		22/08/2022	2 to 3	6:30	16:00
6100235		22/08/2022	2 to 3	8:45	15:35
6100235		22/08/2022	2 to 3	7:00	12:00
6100235		22/08/2022	2 to 3	6:30	16:00
6100235		22/08/2022	2 to 3	8:45	15:35
6100235		22/08/2022	2 to 3	7:00	12:00
6100235		22/08/2022	2 to 3	6:30	16:00
6100235		22/08/2022	2 to 3	8:45	15:35
6100235		22/08/2022	2 to 3	8:45	15:35
6100235		22/08/2022	2 to 3	7:00	12:00

% Increase/Decrease

Centre ID	Age Band	Increase/Decrease
6100235	0 to 2	5%
6100235	2 to 3	-2%
6100235	3 to 5	2%

[AlienSx]

Hello, @DMT19 Try this

let
    data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM0MDAyNlXSUVIAYiMjfQMLfSMDIyMgx0ChJF8BxLCwMgEpMDS1AiqM1SFOk7mVgQFIkxGIJlaTmZUxWJMZKZpGnTfqvKHgPCOQJmMFsnWR4iuELlK8NerCgXchNfwVCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Date = _t, #"Age " = _t, #"Sign In" = _t, #"Sign Out" = _t]),
    inc_dec = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM0MDAyNlXSUTJQKMlXMAIyTFWVYnWQZYxAMsYKQJauEbqcMUgOrAgoEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Centre ID" = _t, #"Age Band" = _t, #"Increase/Decrease" = _t]),
    lookup_tbl = Table.TransformColumnTypes(inc_dec,{{"Increase/Decrease", Percentage.Type}}),
    groups = Table.Group(data, {"ID", "Age "}, {{"Count", each Table.RowCount(_), Int64.Type}, {"rows", each _}}),
    join_lu_tbl = Table.NestedJoin(groups, {"ID", "Age "}, lookup_tbl, {"Centre ID", "Age Band"}, "lookup_tbl", JoinKind.LeftOuter),
    our_table = Table.ExpandTableColumn(join_lu_tbl, "lookup_tbl", {"Increase/Decrease"}, {"Increase/Decrease"}),
    fx_chg_rec = (r as record ) =>
        let 
            c_rows = r[Count],
            change_pct = r[#"Increase/Decrease"],
            id = r[ID],
            tbl = r[rows],
            change = Number.RoundAwayFromZero ( c_rows * change_pct )
        in  if change > 0 
                then Table.Combine( { tbl, Table.FromColumns( {List.Repeat({id}, change)}, {"ID"} ) } )
                else Table.RemoveLastN(tbl, Number.Abs(change)),
    txform_tbl = Table.Combine(Table.TransformRows( our_table, fx_chg_rec))
in
    txform_tbl