Looping in PowerQuery

[cmplieger]

I am trying to run the following Calculation using PowerQuery in PowerBi:

"Service1 Revenue"*"Service1 Rating" + "Service2 Revenue"*"Service2 Rating" + ...

The data comes from these two tables:

╭───╥────────────┬─────────────╮
│ 1 ║   Service  │   Revenue   │
╞═══╬════════════╪═════════════╡
│ A ║ Service 1  │ 10          │
│ B ║ Service 2  │ 100         │
│ C ║ etc        | etc         │
└───╨────────────┴─────────────┘

╭───╥────────────┬─────────────╮
│ 2 ║   Service  │   Rating    │
╞═══╬════════════╪═════════════╡
│ A ║ Service 1  │ 1           │
│ B ║ Service 2  │ 5           │
│ C ║ etc        | etc         │
└───╨────────────┴─────────────┘

I cannot figure out how to write the looping to perform the calculation per service and then sum all the results up.

In excel I guess a vba loop would have worked but not sure how to do that here.

[Greg_Deckler]

There is no "looping" in Power Query, only recursion:

http://social.technet.microsoft.com/wiki/contents/articles/33920.power-query-using-recursion-to-solv...

In your case, you could possibly do a join Merge query step) of the two tables and then a Group By or you could just import the two tables, relate them to one another and do you calculations in DAX.

[cmplieger]

I have linked the two tables together and I understand how I could manually calculate it by using filters in dax to select the row I want. But I am dealing with >100k rows so manually selecting each service is not an option.

[v-sihou-msft · Accepted solution]

@cmplieger

Since you already build the relationship between that two tables, you can directly add the product row by row with SUMX() function as @BhaveshPatel mentioned.

Regards,

[BhaveshPatel]

"But I am dealing with >100k rows so manually selecting each service is not an option."

What do you mean by manually selecting each service.

You can use SUMX and RELATED function in a measure to get the aggrgated total of both columns. In DAX, this looping concept is called ROW CONTEXT and Iterator functions are well suited to do the job. It is bit slower but I guess faster than PowerQuery and VBA looping.

TotalRevenue&Ratings:=SUMX(Table1,Table1[Revenue]*RELATED(Table2[Ratings))