Forum Discussion
Power Query Function (SUMIF)
Hi Expert
I am working in Power Query on Data ( more than 5 files). And I am using one data file (sample table 1) as a base file having a unique ID and want to bring other information from other files based on a Unique ID (Table 2).
| Table 1 | |
| UID | Names |
| 1 | Jojn |
| 2 | Bill |
| 3 | Andry |
| 4 | Colin |
| 5 | Sara |
| Table 2 | |||
| UID | Gross Salary | TAX | Net Salary |
| 1 | 100 | 10 | 90 |
| 2 | 50 | 5 | 45 |
| 1 | 60 | 6 | 54 |
| 3 | 170 | 17 | 153 |
| 2 | 100 | 10 | 90 |
Required Result as below Table
| Required Result | |||||
| UID | Names | Sum of Gross Salary | Sum of TAX | Sum of Net Salary | |
| 1 | Jojn | 160 | 16 | 144 | |
| 2 | Bill | 150 | 15 | 135 | |
| 3 | Andry | 170 | 17 | 153 | |
| 4 | Colin | 0 | 0 | 0 | |
| 5 | Sara | 0 | 0 | 0 |
Challenge:
Other files' unique ID is repeated more than one time due to multiple entries of information.
Can you please help me bring information from other files we used to do in excel by using the sumif formula?
Hi HZAFAR,
You can achieve this with the User Interface.
First go to Table2, select Group By on the ribbon
Set the UID as Key and add aggregations for all other fields (Important! When you have multiple tables, use the Append queries first so you get 1 large table BEFORE this Group By transformation).
Go to Table1, select Merge queries on the ribbon, select UID columns as key in both tables
And finally expand the aggregate columns from the nested table
Done
Ps. If this helps solve your query please mark this post as Solution, thanks!
10 Replies
Hi HZAFAR,
You can achieve this with the User Interface.
First go to Table2, select Group By on the ribbon
Set the UID as Key and add aggregations for all other fields (Important! When you have multiple tables, use the Append queries first so you get 1 large table BEFORE this Group By transformation).
Go to Table1, select Merge queries on the ribbon, select UID columns as key in both tables
And finally expand the aggregate columns from the nested table
Done
Ps. If this helps solve your query please mark this post as Solution, thanks!
Thanks a lot for your quick solution.
one more query on same table 2, I have other column containing text, how i can bring those text column information in nested table, as by using group by >>aggregate option, operator shows only calcuations option i.e SUM, Avg, etc.
But i want to bring text column as well. please share your expert advise.thanks.
Hi HZAFAR,
Create another aggregate column, stick with a sum, it doesn't really matter as long as you bring in the needed field. Then inside the formula bar change the expression from List.Sum into Text.Combine
Now this expression takes an additional parameter a separator, here you can enter "as a text" whatever you want to separate the values.
Hope this is helpful.
Cheers
Merge the two tables using the "Merge Queries" feature in Power Query. Select the "UID" column from both tables as the join key. (Home -->Combine-->Merge Queries)
2. Expand the Merged Table, select the aggregate option for Sum of Gross Salary, Tax and Net
3. The aggregate sum values are displayed but "null" is shown. Have to replace it
4. Select entire table, Power Query -->Transform Data-->Replace Values-->Replace "null" with 0.
I would like to add to this proposed method the following article:
thebiccountant.com | Performance tip for aggregations after joins
Thanks a lot for your quick solution.
one more query on same table 2, I have other column containing text, how i can bring those text column information in nested table, as by using group by >>aggregate option, operator shows only calcuations option i.e SUM, Avg, etc.
But i want to bring text column as well. please share your expert advise.thanks.
