Power Query Function (SUMIF)

[HZAFAR]

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?

[Syndicate_Admin]

1. 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.

[HZAFAR]

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]

I would like to add to this proposed method the following article:

thebiccountant.com | Performance tip for aggregations after joins 

[m_dekorte · Accepted solution]

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!

[HZAFAR]

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

[HZAFAR]

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!

[HZAFAR]

HI ,

I am unable to read the samll adjustment in formula change as the text is too tinly. Would you please write the what has to be adjusted/chmages in formula. 

[m_dekorte]

Hi @HZAFAR,

I've added the code above and will leave it here for you as well

{ “Department”, each Text.Combine( List.Distinct( [Department] ), “, “ ), type nullable text }

BTW you can click on an image to enlarge it.

Ps. If this helps solve your query please mark this post as Solution, thanks!