none
Mutable value (variable)

    Question

  • Is it possible (or will be) to get mutable value in Power Query?
    I mean something like in F# language. I suppose it could be very useful to achieve running total column or something similar.
    I know, we can do this now also, but there are not very efficient ways.

    Regards

    Wednesday, April 15, 2015 10:14 AM

Answers

  • We don't need mutability to make it easier to implement something like a running total, but we definitely need better primitives. In particular, I don't think there's any way to add a running total which doesn't require the entire table to be read into memory -- which is certainly not ideal.

    • Marked as answer by Bill Szysz Thursday, April 16, 2015 12:03 PM
    Thursday, April 16, 2015 11:32 AM
    Owner
  • I do not expect we would ever add such a thing.
    Wednesday, April 15, 2015 3:22 PM
    Owner

All replies

  • I do not expect we would ever add such a thing.
    Wednesday, April 15, 2015 3:22 PM
    Owner
  • We don't need mutability to make it easier to implement something like a running total, but we definitely need better primitives. In particular, I don't think there's any way to add a running total which doesn't require the entire table to be read into memory -- which is certainly not ideal.

    • Marked as answer by Bill Szysz Thursday, April 16, 2015 12:03 PM
    Thursday, April 16, 2015 11:32 AM
    Owner
  • Thanks for your answer, Curt :-)

    Could you propose a way to do "running total" by PQ?
    I avoid  doing it by PQ but sometimes I need it. At this moment I use this code (only exemple)

    let
        Źródło = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
        AddIndex = Table.AddIndexColumn(Źródło, "Indeks", 1, 1),
        #"Dodano kolumnę niestandardową" = Table.AddColumn(AddIndex, "Custom", each List.Sum(List.FirstN(AddIndex[num],[Indeks])))
    in
        #"Dodano kolumnę niestandardową"

    Are there more efficient ways to do this?

    Regards :-)

    Thursday, April 16, 2015 12:02 PM
  • It's a lot more code, but I'd probably implement it like this:

    let
        List.RunningSum = (list) => List.Generate(
            () => [sum = list{0}, position = 1],
            (state) => state[position] <= List.Count(list),
            (state) => [sum = state[sum] + list{state[position]}, position = state[position] + 1],
            (state) => state[sum]),
        Table.AppendColumn = (table, columnName, values) =>
            Table.TransformColumns(Table.AddIndexColumn(table, columnName), {columnName, (i) => values{i}}),
        Table.AppendRunningSum = (table, sourceColumn, newColumn) =>
            Table.AppendColumn(table, newColumn, List.RunningSum(List.Buffer(Table.Column(table, sourceColumn)))),
        Source = Table.FromColumns({{1, 2, 3}, {4, 5, 6}}),
        WithSum = Table.AppendRunningSum(Source, "Column1", "Column1Sum")
    in
        WithSum

    This defines a new function that takes a list of numbers and returns a running sum of those numbers, another new function that takes a table and a list and pastes the list as a new column onto the table, and a third function that simply composes the two.
    Thursday, April 16, 2015 1:26 PM
    Owner
  • Hi Curt :-)

    Thanks for your flexible solution. I appreciate it and i'm grateful.
    You gave me an idea to use buffer.
    In this way, the speed grows up.
    If I understand correctly M code, there are many buffered lists.
    I rewrite my code and now I use only one buffered list.

    let
        Source = Table.FromColumns({{1..10000}},{"Value"}),
        AddIndex = Table.AddIndexColumn(Source, "Indeks", 1, 1),
        List = List.Buffer(AddIndex[Value]),
        AddColumn = Table.AddColumn(AddIndex, "Running Total", each List.Sum(List.FirstN(List,[Indeks]))),
        RemoveIndexColumn = Table.RemoveColumns(AddColumn,{"Indeks"})
    in
        RemoveIndexColumn

    Try your and my code for 10 000 lines of data... there is a big (not very big) speed difference and CPU usage. So we can choose... flexibility or efficiency :-)
    Thank you very much for your time and sorry for my english.

    Regards

    Thursday, April 16, 2015 5:05 PM