Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create table resulting from the analysis of another table

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...
Need Help
  • artemus's avatar
    artemus
    6 years ago
    (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

artemus's avatar
artemus
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
(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