Create table resulting from the analysis of another table

[Anonymous]

Hi everyone!

I'm trying to move my excel skills into PowerBI for profesionnal reasons.

Here is my case : I have a table of Non Conformity with creation and closure date columns. My goal is to know the quantity of opened Non Conformity at a given time. Exemple : "On the 1st february, we had 20 non conformity opened, but on the 5st, we had 75 opened -> What happened?'. The final goal is to draw the curve with time on X axis and quantity of Non conformity opened in Y axis.

To me, I need to create a table with Date, Non conformity ID and Status of the non conformity at that same date.

Why I am struggling : I perfectly know how to do that in VBA using loops, but I can't figure out how M language works for this kind of situation, despite all the artcles I read on the subject so far… I've to admit that I'm still using the basic button to transform my data in PowerBI instead of dealing with the M language directly, and this must changes 🙂

Thanks for you help! Alexis

[artemus · Accepted solution]

(tbl as table, col as text, newCol as text) as table => 
   let 
      range = {0 .. Table.RowCount(tbl)},
      runningTotal = List.Accumulate(range, {}, (current, next) => current & {List.Last(current,  0) + Record.Field(tbl{next}, col)}),
      asTable = Table.FromList(runningTotal, each {_}, 1, {newCol}),
      joinTable = Table.AddIndexColumn(asTable, "t_index_join"),
      withIndexColumn = Table.AddIndexColumn(tbl, "t_index"),
      joined = Table.Join(withIndexColumn, "t_index", joinTable, "t_index_join"),
      removedTempCols = Table.RemoveColumns(joined, {"t_index", "t_index_join"})
   in
      removedTempCols

Here is how you do a running sum as a function