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!
Resident RockstarHi 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.
- m_dekorte
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
HI Dear,
I tried but could not succedded, please see below query with data example and suggest with screenshot for better understanding.
Table 1 UID Names 1 Jojn 2 Bill 3 Andry 4 Colin 5 Sara Table 2 UID Gross Salary TAX Net Salary Department 1 100 10 90 Finance 2 50 5 45 HR 1 60 6 54 Finance 3 170 17 153 Sales 2 100 10 90 HR Required Result UID Names Sum of Gross Salary Sum of TAX Sum of Net Salary Department 1 Jojn 160 16 144 Finance 2 Bill 150 15 135 HR 3 Andry 170 17 153 Sales 4 Colin 0 0 0 5 Sara 0 0 0 - m_dekorte
Hi HZAFAR,
Here the grouping steps in images, if Department is unique for each UID you could add it to the grouping (A) - otherwise this approach will retrieve all unique Departments (B).
Initially that will return an error, as shown here
Small adjustment to the code will fix that
{ “Department”, each Text.Combine( List.Distinct( [Department] ), “, “ ), type nullable text }
Now the aggregated Table2 can be merged with the UID from Table1
Expand the fields of interest from the nested table
With this result
Ps. If this helps solve your query please mark this post as Solution, thanks!