Forum Discussion

DMT19's avatar
DMT19
Regular Visitor
3 years ago

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

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:

IDNameDateAge Sign InSign Out
6100235 22/08/20220 to 28:4515:35
6100235 22/08/20220 to 27:0012:00
6100235 22/08/20220 to 26:3016:00
6100235 22/08/20220 to 28:4515:35
6100235 22/08/20220 to 27:0012:00
6100235 22/08/20220 to 26:3016:00
6100235 22/08/20220 to 28:4515:35
6100235 22/08/20220 to 27:0012:00
6100235 22/08/20220 to 26:3016:00
6100235 22/08/20220 to 28:4515:35
6100235 22/08/20220 to 27:0012:00
6100235 22/08/20220 to 26:3016:00
6100235 22/08/20220 to 28:4515:35
6100235 22/08/20220 to 27:0012:00
6100235 22/08/20220 to 26:3016:00
6100235 22/08/20220 to 28:4515:35
6100235 22/08/20222 to 3 8:4515:35
6100235 22/08/20222 to 3 7:0012:00
6100235 22/08/20222 to 3 6:3016:00
6100235 22/08/20222 to 3 8:4515:35
6100235 22/08/20222 to 3 7:0012:00
6100235 22/08/20222 to 3 6:3016:00
6100235 22/08/20222 to 3 8:4515:35
6100235 22/08/20222 to 3 7:0012:00
6100235 22/08/20222 to 3 6:3016:00
6100235 22/08/20222 to 3 8:4515:35
6100235 22/08/20222 to 3 8:4515:35
6100235 22/08/20222 to 3 7:0012:00

 

% Increase/Decrease

Centre IDAge BandIncrease/Decrease
61002350 to 25%
61002352 to 3 -2%
61002353 to 52%
Need Help

1 Reply

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    3 years ago

    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