Looping in PowerQuery

Question

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.

Answer 1

Here are some steps you can follow:

1. Merge the queries together using the Merge Queries ribbon button in the Query Editor.
2. Choose the other table, click on the Service column on both tables, and click OK.
3. Click on the expand button next to the new column's name, and choose the Revenue or Rating column. Click OK.
4. Click on the Rating column, then control+click on the Revenue column, and then right-click on the column and choose "Product". You can also access this through the ribbon in Add Column | Standard | Multiplication. If the product context menu item isn't available (if you're using an older version of Power Query), you can also add a custom column with the formula [Rating] * [Revenue].
5. You can either drill down on the new multiplication column and modify the query directly, or add a new custom step with the fx button next to the formula bar and put something like this in this formula bar: List.Sum(previous_step_name[new_column_name]).

Answer 2

After the multiplication (step 4 in previous answer) you can select the service column, choose Group By, add a new field name, choose sum and the column with the multiplied values.

Full code from Excel (imagine SQLData is from SQL); after each step I adjusted the step name:

let
    Source = Excel.CurrentWorkbook(){[Name="SQLData"]}[Content],
    SQLData = Table.TransformColumnTypes(Source,{{"Service", type text}, {"Revenue", Int64.Type}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Ratings"]}[Content],
    Ratings = Table.TransformColumnTypes(Source2,{{"Service", type text}, {"Rating", Int64.Type}}),
    Joined = Table.NestedJoin(SQLData,{"Service"},Ratings,{"Service"},"NewColumn",JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Joined, "NewColumn", {"Rating"}, {"Rating"}),
    Multiplication = Table.AddColumn(Expanded, "Inserted Multiplication", each List.Product({[Revenue], [Rating]}), Int64.Type),
    Renamed = Table.RenameColumns(Multiplication,{{"Inserted Multiplication", "Rated Revenue"}}),
    Grouped = Table.Group(Renamed, {"Service"}, {{"Rated Revenue by Service", each List.Sum([Rated Revenue]), type number}})
in
    Grouped

Answer 3

If I correctly understood your question, you need a Grand Total on Revenues*Ratings. Then you can use following code:

   let
       Source1 = /* Put your code fetching data from DB instead of #table()*/ 
                 #table({"1", "Service", "Revenue"}, {{1, "Service1", 10}, {2, "Service2", 100}}), 
       Source2 = /*This is your Excel table. Put respective Excel.CurrentWorkbook() instead of #table()*/ 
                 #table({"2", "Service", "Rating"}, {{1, "Service1", 1}, {2, "Service2", 5}}), 

       JoinTables = Table.Join(Source1, {"Service"}, Source2, {"Service"}, JoinKind.Inner), 
       AddProduct = Table.AddColumn(JoinTables, "Product", each [Revenue]*[Rating]),
       Group = Table.Group(AddProduct, {}, {{"Grand Total", each List.Sum([Product]), type number}})
   in
       Group