Forum Discussion
razieh12
2 years agoFrequent Visitor
SUM OF PREVIOUS columns , SAME ROW, conditional formating
Hello I'm currently working on a task and would appreciate your assistance. I'm managing three cities, each with a fixed bicycle capacity. Additionally, there are eight bicycle stations, and the ...
- 2 years ago
There is an issue with City3 - the mark should be on station G, not H.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4sqTRU0lEyMgAShkYgwhhEgLjmIIYpkLAAMcACsToQLUYw1eZQKRMgBmk0gtKmYHGYcmOoISZQFSZQXUZIuoCqYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Capacity = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t, H = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"City", "Capacity"}, "Station", "Reported"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Capacity", Int64.Type}, {"Reported", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"City"}, {{"Rows", each _, type table [City=nullable text, Capacity=nullable number, Station=text, Reported=nullable number]}}), Compute = (tbl)=> let #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Full", each List.Accumulate({0..[Index]},[cumul=0],(state,current)=>[ cumul = if #"Added Index"[Capacity]{current}<=state[cumul] + #"Added Index"[Reported]{current} then 0 else state[cumul] + #"Added Index"[Reported]{current}])) in #"Added Custom", AddedCompute = Table.AddColumn(#"Grouped Rows","comp",each Compute([Rows])), #"Expanded comp" = Table.ExpandTableColumn(AddedCompute, "comp", {"Capacity", "Station", "Reported", "Full"}, {"Capacity", "Station", "Reported", "Full"}), #"Expanded Full" = Table.ExpandRecordColumn(#"Expanded comp", "Full", {"cumul"}, {"cumul"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Full",{"City", "Capacity", "Station", "Reported", "cumul"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Capacity", Int64.Type}, {"Station", type text}, {"Reported", Int64.Type}, {"cumul", Int64.Type}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
The yellow mark is equivalent to cumul=0
lbendlin
2 years agoSuper User
There is an issue with City3 - the mark should be on station G, not H.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4sqTRU0lEyMgAShkYgwhhEgLjmIIYpkLAAMcACsToQLUYw1eZQKRMgBmk0gtKmYHGYcmOoISZQFSZQXUZIuoCqYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Capacity = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t, H = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"City", "Capacity"}, "Station", "Reported"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Capacity", Int64.Type}, {"Reported", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"City"}, {{"Rows", each _, type table [City=nullable text, Capacity=nullable number, Station=text, Reported=nullable number]}}),
Compute = (tbl)=> let
#"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Full", each List.Accumulate({0..[Index]},[cumul=0],(state,current)=>[ cumul = if #"Added Index"[Capacity]{current}<=state[cumul] + #"Added Index"[Reported]{current} then 0 else state[cumul] + #"Added Index"[Reported]{current}]))
in #"Added Custom",
AddedCompute = Table.AddColumn(#"Grouped Rows","comp",each Compute([Rows])),
#"Expanded comp" = Table.ExpandTableColumn(AddedCompute, "comp", {"Capacity", "Station", "Reported", "Full"}, {"Capacity", "Station", "Reported", "Full"}),
#"Expanded Full" = Table.ExpandRecordColumn(#"Expanded comp", "Full", {"cumul"}, {"cumul"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Full",{"City", "Capacity", "Station", "Reported", "cumul"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Capacity", Int64.Type}, {"Station", type text}, {"Reported", Int64.Type}, {"cumul", Int64.Type}})
in
#"Changed Type1"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
The yellow mark is equivalent to cumul=0
razieh12
2 years agoFrequent Visitor
Thanks, it works