Sumif in Table.group power query

[lavanyak]

Hello,

How can I use SUMIF in Table.Group, i,e I want to group the data conditionally.Below is the sample data and output I need:

Group	Sales	Flag
A	1000	1
A	500	0
A	1000	1
B	1400	1
B	1600	1
C	5000	1

For this data, I need sum of sales by Group where Flag is 1;

Output I need is:

Group	Sales	Flag
A	2000	1
B	3000	1
C	5000	1

We have something like this 

= Table.Group(Rategain_PivotN,"Join_key",{"AvgCheapestPrice-EZ", each List.SUM([Cheapest Price])})

But I need something which will allow me to group data by join _key and sum based of flag=1.So is there a function called List.Sumif in power query where I can use it in Table.group.

Regards

Lavanya

[v-frfei-msft]

Hi @lavanyak,

Based on my test, we can use Group By feature in power query. Firstly, we need filter the table, after that we can use the group By function to  get the result as below.

The backend M formula is below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MDAAUUqxOhABUzDfAM5HUeAEYpmgC5ghCThDjIAKxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Group " = _t, Sales = _t, Flag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group ", type text}, {"Sales", Int64.Type}, {"Flag", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Flag] = 1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Group "}, {{"Count", each List.Sum([Sales]), type number}, {"flag", each List.Max([Flag]), type number}})
in
#"Grouped Rows"

For more details, please check the pbix as attached.

https://www.dropbox.com/s/om76kw1yrejkjl4/Sumif%20in%20Table.group%20power%20query.pbix?dl=0

Regards,

Frank

[lavanyak]

Thanks for your response.

I need something like List.SUMIF, because for few fields I need total sum irrespective of whatever value flag takes and few other fields I need sum where flag is 1.

If I filter the records bfr grouping then I wont be able to get sum of all values where flag is 1,2,3 etc.

[v-frfei-msft]

Hi @lavanyak,

Here we can add a conditional column in advance to get all the values of flag you need.

= Table.AddColumn(#"Changed Type", "Custom", each if [Flag] <> 0 then [Sales] else null)

After that, we can we can use Group By function as before.

The backend M formula is below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MDAAUUqxOhABUzDfAM6HKjACCziBBEyQdIAFzJBUOEOMgKqIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Group " = _t, Sales = _t, Flag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group ", type text}, {"Sales", Int64.Type}, {"Flag", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Flag] <> 0 then [Sales] else null),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Group "}, {{"Count", each List.Sum([Sales]), type number}, {"asd", each _, type table}})
in
    #"Grouped Rows"

For more details, please check the pbix as attached.

https://www.dropbox.com/s/f9gcjkbtt7r5zhy/Sumif%20in%20Table.group%20power%20query2.pbix?dl=0

Regards,

Frank

[v-frfei-msft]

Hi @lavanyak,

Does that make sense? If so , kindly mark the answer as a solution to close the case, thanks in advance.

Regards,
Frank

[Anonymous]

Could you simply filter out anything with FLAG = 0 on the previous statement, then use LIST.SUM?

[MFelix]

Hi @lavanyak,

In Power Query you should first filter the Flag for values equal 1 and then make the group by Group.

This will give the expected result.

Regards,

MFelix