Forum Discussion
Calculating a increase in faults across multiple Days/Dates
Hi Anonymous,
Sorry for the delay, I was working on a deadline project in the last few weeks.
I think you get the idea right. "Closing" column is the "closing balance" (number of faults) for the day as you have it as runing total. It eaquals to the number of faults on the next day. Of course your rows should be sorted by the Date column.
You can apply if to the mixed unique items in the same table by using Table.Group.
This example a bit messy, but it gives you an idea where to go:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWMzDUMzIwMlbSUXIrykxJrAQyTAwMgKR/UWJeeqpSrA5QmQmSsuDEktIiiEJLDIVYzTMFqXIsKMjBb5qRAZK6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Days = _t, #"Number of faults" = _t, Fruit = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Days", type text}, {"Number of faults", Int64.Type}}), vt = Value.Type(Table.AddColumn(#"Changed Type", "Closing", each null, type number )), #"Grouped Rows" = Table.Group(#"Changed Type", {"Fruit"}, {{"Data", each let Combine = Table.ToColumns(_) & {List.Skip(_[Number of faults])}, Format = Table.FromColumns(Combine, vt) in Format }}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Days", "Number of faults", "Closing"}, {"Date", "Days", "Number of faults", "Closing"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Date", "Days", "Number of faults", "Fruit", "Closing"}), Custom1 = #table(vt, Table.ToRows(#"Reordered Columns")), #"Added Custom" = Table.AddColumn(Custom1, "Increase", each [Closing]-[Number of faults]) in #"Added Custom"
Hi jbwtp John,
Many thanks once again for your time and the code. Due to the confidentiality of the data I had to reword them in my example. I have taken your new code above and changed the table name and faults accordingly to match my table and column name. I may have misunderstood so I apologise. Where it says closing my main goal though I am not sure if this is feasible was too look at these incidents against a unique identifer I have in the shaded column and too see what the increase was on Saturday against Friday but I am a little unsure as to what the closing column output numbers represent. I do apologise that I have already taken up so much of your time. So what I mean is say for example had the hidden unique column been named fruits so I have apples and oranges. I would like to see;
Fruit name Incident Count Date Days Increment/Decrement
Oranges 50 30.12.2022 Friday 150
Oranges 200 31.12.2022 Saturday
In my data Fruits and it's values are different but I'm not sure if I am able to apply this principle just to change the data headings in the code.
Memorable MemberHi Anonymous,
Sorry for the delay, I was working on a deadline project in the last few weeks.
I think you get the idea right. "Closing" column is the "closing balance" (number of faults) for the day as you have it as runing total. It eaquals to the number of faults on the next day. Of course your rows should be sorted by the Date column.
You can apply if to the mixed unique items in the same table by using Table.Group.
This example a bit messy, but it gives you an idea where to go:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWMzDUMzIwMlbSUXIrykxJrAQyTAwMgKR/UWJeeqpSrA5QmQmSsuDEktIiiEJLDIVYzTMFqXIsKMjBb5qRAZK6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Days = _t, #"Number of faults" = _t, Fruit = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Days", type text}, {"Number of faults", Int64.Type}}),
vt = Value.Type(Table.AddColumn(#"Changed Type", "Closing", each null, type number )),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Fruit"}, {{"Data", each
let
Combine = Table.ToColumns(_) & {List.Skip(_[Number of faults])},
Format = Table.FromColumns(Combine, vt)
in Format
}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Days", "Number of faults", "Closing"}, {"Date", "Days", "Number of faults", "Closing"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Date", "Days", "Number of faults", "Fruit", "Closing"}),
Custom1 = #table(vt, Table.ToRows(#"Reordered Columns")),
#"Added Custom" = Table.AddColumn(Custom1, "Increase", each [Closing]-[Number of faults])
in
#"Added Custom"