I’m working in Excel and in Power Query Editor.
I have two columns that are tables. Right now, those two columns look something like this:
Column name: Ratings | Weights
Column value: Table| Table
For Ratings:
Products | Driver 1 | Driver 2 | Driver 3
Foobar | 5 | 2 | 5
For Weights:
Products | Weight 1 | Weight 2 | Weight 3
Foobar | 33 | 33 | 33
How can I create a new column to multiply these two columns so that the output would be:
Column name: Score
Column value: (533+233+5*33)
@anonymous236 , welcome to the forum !
What is exact the connection between the tables ? Cq what is the rule of coming to the example scores? Is it the sequence number, like 1, 2 and 3 in this example, Driver and weight 1 belongs together? Score1 = driver 1 x weight 1 ?
My first thought to the solution is using the Power Query Merge function.
* 1. No creo estar claro en lo que necesitas. De todas formas, hice este query para calcular un score final por auto.
Uní las dos tablas principales en un solo query, en este caso "tblWeights",
pero se puede hacer en cualquiera.de las dos.
El proceso es mayormente basado en UI, para que sea fácil de revisar.
Espero que esto sea lo que buscas...
* 2. let
* 3. Pesos = Excel.CurrentWorkbook(){[Name="tblWeights"]}[Content],
* 4. UnpPesos = Table.UnpivotOtherColumns(Pesos, {"Car"}, "Attribute", "Weights"),
* 5. Rates = Excel.CurrentWorkbook(){[Name="tblRatings"]}[Content],
* 6. UnpRates = Table.UnpivotOtherColumns(Rates, {"Car"}, "Attribute", "Ratings"),
* 7. Merge = Table.NestedJoin(UnpRates, {"Car", "Attribute"}, UnpPesos, {"Car", "Attribute"}, "UnpPesos", JoinKind.LeftOuter),
* 8. ExpUnpPesos = Table.ExpandTableColumn(Merge, "UnpPesos", {"Weights"}, {"Weights"}),
* 9. Scores = Table.AddColumn(ExpUnpPesos, "Score", each [Ratings]*[Weights]),
* 10. Sol = Table.Group(Scores, {"Car"}, {{"Score", each List.Sum([Score])}})
* 11. in
* 12. Sol
An alternative possible solution is :
Weighting calculation PQE.pbix (37.3 KB)
KR Delta Select
