Forum Discussion
New Table from Existing Tables
I have two existing tables that each have their own unique/separate queries, and they both have the exact same structure.
Table 1:
Month Rate Type
Jan .05 Base
Table 2:
Month Rate Type
Jan .01 TLP
I want to create a new table that sums the "Rate" from each table for each of the respective months.
Use this code:
let Source = Table.Combine({T_Table1, T_Table2}), GroupedTable = Table.Group( Source, {"Month"}, {{"RateType1", each List.Sum([RateType1]), type number}, {"RateType2", each List.Sum([RateType2]), type number}, {"RateType3", each List.Sum([RateType3]), type number}} ) in GroupedTable
5 Replies
_AAndradeResident Rockstar
Hi,
In power query use this code:let Source = Table.Combine({T_Table1, T_Table2}), // Replace T_Table1 and T_Table2 with the names of your tables #"Grouped Rows" = Table.Group(Source, {"Month"}, {{"Sum", each List.Sum([Rate]), type nullable number}}) in #"Grouped Rows"
The final result should be this:That worked perfect. Not to complicate things, but what if I then had multiple rate types that I wanted to sum together?
Table 1:
Month RateType1 RateType2 RateType3
Jan .05 .035 .075
Table 2:
Month RateType1 RateType2 RateType3
Jan .025 .015 .035
- _AAndrade
You want to sum by each type or sum all types in one line?
For example you want:
Jan 0.075 0.050 0.11
Or
Jan 0.235
?Table 1:
Month RateType1 RateType2 RateType3
Jan .05 .035 .075
Table 2:
Month RateType1 RateType2 RateType3
Jan .025 .015 .035
New Table:
Month RateType1 RateType2 RateType3
Jan .075 .05 .11
- _AAndrade
Use this code:
let Source = Table.Combine({T_Table1, T_Table2}), GroupedTable = Table.Group( Source, {"Month"}, {{"RateType1", each List.Sum([RateType1]), type number}, {"RateType2", each List.Sum([RateType2]), type number}, {"RateType3", each List.Sum([RateType3]), type number}} ) in GroupedTable