Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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% |
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