How to sum if in Power Query

[KH_Mike]

Hi All,

I have two table with linkup key. Table A is store for the transction, table B is store for the breakdown for each transaction PL information. Like below two tables.

Table 1

Linkup.Key	YearMonth	Product	Amount
1111111111	202201	A	10
1111111112	202201	B	9
1111111113	202201	A	8
1111111114	202202	C	7
1111111115	202203	D	6

Table 2

Linkup.Key	Type	Price
1111111111	Revenue	100
1111111112	Cost	90
1111111113	Revenue	80
1111111114	Cost	70
1111111115	Revenue	60
1111111111	Cost	50
1111111112	Revenue	40
1111111113	Cost	30
1111111114	Revenue	20
1111111115	Cost	10
1111111111	Revenue	21

Before that I load both tables and calculate the related Revenue / Cost for each Product (Key). Since the dataset become bigger and bigger, I want to know if there is any way to calculate Revenue / Cost and make it on table A so that I don't need to load the Table 2.

Thank you.

[SpartaBI · Accepted solution]

@KH_Mike 
Yes you can.
This is one example of a code that will do that:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8snMyy4t0PNOrVTSUYpMTSzyzc8ryQCyA4ryU0qTS4Asx9z80rwSpVidaCVDOACKGxkYGRmAGI5AbGiAqsAIWYETEFuiyhujG2CBKm8CkwcZ5AzE5qjypjB5kEEuQGymFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Linkup.Key", Int64.Type}, {"YearMonth", Int64.Type}, {"Product", type text}, {"Amount", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Linkup.Key"}, Table2, {"Linkup.Key"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Type", "Price"}, {"Type", "Price"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([Type] = "Revenue")),
    #"Merged Queries1" = Table.NestedJoin(#"Filtered Rows", {"Linkup.Key"}, Table2, {"Linkup.Key"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries1", "Table2", {"Type", "Price"}, {"Type.1", "Price.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Table1", each ([Type.1] = "Cost")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Price", "Revenue"}, {"Price.1", "Cost"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Type", "Type.1"})
in
    #"Removed Columns"

What I did is to bring both your tables and then I continued transform table1 so the result is still in table1.
Take only the steps starting from the first Merge, you will probably need to change the names to your names in the query.
Aftet that, just disable the load for table 2.
In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂