Forum Discussion

razieh12's avatar
razieh12
Frequent Visitor
2 years ago
Solved

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 ...
  • lbendlin's avatar
    lbendlin
    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