Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create sumif in PowerQuery (not DAX, and not use group by feature)

I have a step in PowerQuery where I want to do a sumif (like in Excel). See the below picture.   In Power Query I have Field 1 and Field 2 and I want to add what is in column C.  Column C sums colu...
Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    Hi Anonymous ,

     

    Try this code for a custom column:

     

     

    let _item = [Column1] in
    List.Sum(
    Table.SelectRows(#"Changed Type", each [Column1] = _item)[Column2])

     

     

     

    Change the bold part for the last step name.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello, I tried your query but it shows the following error: "Expression.Error: A cyclic reference was encountered during evaluation."

 

This is my query:

 

= let
_Item = [#"ID"]
in
List.Sum(
Table.SelectRows(#"DATA",each
[#ID"] = _Item)[#"COLLECTIONS"])

 

Table Name = Data

Column with ID number = ID (I have different collections for the same ID (differente rows) that I want to summarize)

Column with collection numbers = COLLECTIONS

 

Basically I want to add all the collections for every single ID, and show the total in every row (according to that ID).

 

Any thoughts on how to fix it?

 

Thank you.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I have the same error. Did you ever get this fixed?