SUMIFS functionality using Power Query M advanced editor

[Blackban88s]

Hello all, in need of some "M" wizardry to recreate the functionality of this SUMIFS formula. My current skill level with "M" consists of hastily key-word searching the M formula language .pdf so a SUMIF is way over my head. I'm using this SUMIF to compute a running daily balance based on each days transactions. This is the SUMIFS in question:

Where "[Index]" is the table row count (1...n+1) in ascending order.

"=SUMIFS([Total Amt],[Acct No. Short],[@[Acct No. Short]],[Value Date],"<="&[@[Value Date]],[Index],"<="&[@Index])"

Is this possible to do? The current table formula is working fine except when refreshing the query, the table formula doesn't automatically copy-down to the newly added rows of the table.

 

[Ozeroth]

Hi,
It can definitely be done.

There is a similar sort of example on Ken Puls' Blog here:
Create Running Totals in Power Query


Here's how I would do it, but I'm sure there are other ways!

I'm assuming your source table has these columns:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Acc. No Short[/TD]
[TD]Value Date[/TD]
[TD]Total Amt[/TD]
[/TR]
</tbody>[/TABLE]



Here is some M code that should do what you've described - test it out (assuming the source table is named Table1 in Excel):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Acc No. Short", Int64.Type}, {"Value Date", type date}, {"Total Amt", type number}}),


[COLOR=#ff0000][B]    // Start function definition[/B]

[B]    MySUMIFFunction = (InputTable as table, MyDate as date, MyAccount as number, MyIndex as number) as number =>[/B]
[B]                      let[/B]
[B]                         FilteredTable = Table.SelectRows(InputTable, each ([Value Date] <= MyDate and [Acc No. Short] = MyAccount and [Index] <= MyIndex)),[/B]
[B]                         SumOfAmt = List.Sum( Table.Column(FilteredTable, "Total Amt") )[/B]
[B]                      in[/B]
[B]                         SumOfAmt,[/B]

[B]   // End function definition [/B]
[/COLOR]

    AddedSUMIFS = Table.AddColumn(#"Changed Type", "SUMIFS", each MySUMIFFunction(#"Changed Type", [Value Date], [Acc No. Short], [Index]))
in
    AddedSUMIFS

The important bit is the "MySUMIFFunction" function definition in red.
The code basically defines the function to sum Total Amt over rows with Index <= current row, Date <= current row and Account = current row.
Then it adds a column to the original table, calling this function for every row.

 

[miguel.escobar]

this might help you:
ABC Analysis with Power Query - Powered Solutions

the step #5 should give you everything that you need to do a SUMIF:
List.Sum( List.Select(MyList, (x) => x>=[Total Sales])))

 

[DickyMoo]

Not sure why the OP never responded, but this has helped me enormously, thank you both.

Rich