SUMIFS functionality using Power Query M advanced editor

Blackban88s

New Member
Joined
Dec 3, 2011
Messages
10
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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:

IndexAcc. No ShortValue DateTotal Amt

<tbody>
</tbody>



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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top