Forum Discussion
Power Query Function (SUMIF)
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!
Administrator
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.
- m_dekorte
Resident Rockstar
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.
