Add columns to query to contain equivalent of excel sumif based on column heading and other query

[SMY]

Hi,

I’m only just getting to grips with Power Query and was wondering if someone could perhaps point me in the right direction on a particular topic as I’m not sure if I’m even thinking about it in the right way coming very much from an excel base previously.

I’ll try describe it as simply as I can but the situation is as follows:

I have a SQL Server Database and have built a series of queries using the Power Query function in Excel to extract the bare bones of the data required from a series of tables.

In my SQL Database, there are two key tables:

1. a transaction table (A) that houses some basic details of each deal or transaction such as who the customer is etc. These transactions are akin to loans so they start out as one basic value but that goes up and down over time.
2. a cashflow table (B) that then shows all of the individual transactions over time that related to one over deal.

Each transaction / deal will have a series of past and future cashflows mapped out in the cashflow table.

3.) I have then created a custom series of dates via another query whereby I, for example start today and create a list of month-end dates starting today for 36 months.

By way of rough example, my deal table (A) would be:

Deal_Number	Client_Name	Deal_Type	Amount	Start_Date	End_Date
123	ABC	Sample_Deal	-10000	01/01/2020	31/12/2022

My cashflow table would look like (B):

Deal_Number	Cash_Flow_Type	Date	Amount	Criteria_Field
123	Initial_CF	01/01/2020	-10,000	B
123	Second_CF	10/08/2020	-10,000	B
123	Interest_Charged	15/10/2020	-125	B
123	Repay	20/11/2020	7,000	B
123	Final Repay	25/12/2021	13,125	B

My calculated dates list would look like:

30/11/2020, 31/12/2020, 31/01/2021………

In excel terms what I would basically like to do is append a series of columns to table A with each of the dates generated as column headers.

Then for each of those new date columns in Table A, I would like to populate it by reference to the data in Table B in what (again in excel terms) would basically be a sumif statement (so the sum of the amount field whereby the data is less than or equal to the reference date for that column but meeting criteria based on other fields in table b also).

I envisage the output as looking something like

Deal_Number	Client_Name	Deal_Type	Amount	Start_Date	End_Date	30/11/2020	Dd/mm/yyyy	Dd/mm/yyyy
123	ABC	Sample_Deal	-10000	01/01/2020	31/12/2022	Sumif based on Table b	…	…

Does anyone have any tips or pointers for me as to how to approach this? I’ve been messing around with parameters, formulas etc but am a bit lost as to how to go about it at this point.

Any help would be very much appreciated.

[SMY]

Hi Jimmy. Thanks again for taking the time to look at this for me. I've been working through your suggestion and it's very good but I'm left with a remaining question as to how I could do a sumif on table B based on the column headings. I very likely explained it poorly but if I take one of the month ends (Say 30/11/2020), when that month end gets appended to the original deals table, I'd need to value in each row below that to effectively (in excel terms) be a sumif of table B based on a series of criteria (including the column header). Taking the same month end, I would want the value for the row to be the sum of all the amount values in table b meeting the criteria that the data is less than or equal to my column header (30/11/2020) and where the deal number matches and say the criteria field equals "B". Hopefully that's a little clearer as to what I'm trying to do. The difference is the solution above just seems to pivot the values based on on given month end and doesnt seem to sum in the earlier dates. 

[Jimmy801]

Hello @SMY 

Sorry, I can not follow you. What I did is exactly reproducing your desired output 

to explain in my words is to join table A with B and then puting the EOF of table B in columns usind a sum on the values on Table B. So it's a Sumif (considering dealnumber and using the EOF as columns, summing the value).

Could you please give me the desired output, considering my tables in the example.

BR

Jimmy 

[SMY]

Thank you very much Jimmy. Really appreciate you taking the time to look at that for me. I will work my way through it and see how that goes.

[Jimmy801]

Hello @SMY 

I don't know if I got you right.

you could first calculate the end of month of your tableB, then join with table a, expand the end of month and the mount value, and then pivoting the end of month using the amount.1 for summing. Here a easy example

let
    A = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcnRyBpLBibkFOanxLqmJOUCerqEBEAAZBoZ6QGRkYATiGBvqGRqBOEZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Deal_Number = _t, Client_Name = _t, Deal_Type = _t, Amount = _t, Start_Date = _t, End_Date = _t]),
    ChangeTypeA= Table.TransformColumnTypes(A ,{{"Deal_Number", Int64.Type}, {"Client_Name", type text}, {"Deal_Type", type text}, {"Amount", Int64.Type}, {"Start_Date", type date}, {"End_Date", type date}}),
    B = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJR8szLLMlMzIl3dgNyDAz1gMjIwMgAyNE1BJFOSrE6MLXBqcn5eSkQpYYGegYWuJV65pWkFqUWl8Q7ZyQWpaemgHSY6gE1wXUYmaJpCUotSKwE0kYGeoZwR5ijKXLLzEvMUYArBRppBFJqCDLfWAduaCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Deal_Number = _t, Cash_Flow_Type = _t, Date = _t, Amount = _t, Criteria_Field = _t]),
   ChangeTypeB = Table.TransformColumnTypes(B,{{"Deal_Number", Int64.Type}, {"Cash_Flow_Type", type text}, {"Date", type date}, {"Amount", type number}, {"Criteria_Field", type text}}, "de-DE"),
    AddEndOfMonth = Table.AddColumn(ChangeTypeB, "EndOfMonth", each Date.EndOfMonth([Date]), type date),
    JoinAB = Table.NestedJoin
    (
        ChangeTypeA, 
        "Deal_Number",
        AddEndOfMonth,
        "Deal_Number",
        "B"
    ),
    #"Expanded B" = Table.ExpandTableColumn(JoinAB, "B", {"Amount", "EndOfMonth"}, {"Amount.1", "EndOfMonth"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded B", {{"EndOfMonth", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Expanded B", {{"EndOfMonth", type text}}, "de-DE")[EndOfMonth]), "EndOfMonth", "Amount.1", List.Sum)
in
    #"Pivoted Column"

Be aware that in order to reproduce your scenario I had to create within this query two tables

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy