Forum Discussion

zaza's avatar
zaza
Resolver III
5 years ago
Solved

Table.Profile sum based on another column

I'm stuck with something that seems very simple but for some reason I can't seem to be able to make it work.

 

I have the following table:

 

I create a new query in order to do a table profile on this table:

 

= Table.Profile(Query3, {{"Custom", each Type.Is(_, type nullable number), each List.Sum(    List.Select( _, each _ = 1 )    )}})

 

 

As you can see I have a custom column that sums up the values if they are equal to 1.

 

However I want to get the sum of the values in Column1 if the values in the other columns equal to 1, basically a SUM IF of Column1. The resulting column should contain: 4, 42, 32 

 

How can I achieve this?

 

Here is the full query to generate the table and current result:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyBGEDAwOlWJ1oCAdFALcSfPIGBpiGQMTQ1eBQYICwJRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    Custom1 = Table.Profile(#"Changed Type", {{"Custom", each Type.Is(_, type nullable number), each List.Sum(    List.Select( _, each _ = 1 )    )}})
in
    Custom1

 

 

  • ImkeF's avatar
    ImkeF
    5 years ago

    Hi zaza ,

    please try this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyBGEDAwOlWJ1oCAdFALcSfPIGBpiGQMTQ1eBQYICwJRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    Custom1 = Table.Profile(#"Changed Type"),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each List.Sum(Table.SelectRows(#"Changed Type", (inner) => Record.Field(inner, [Column]) = 1)[Column1]))
in
    #"Added Custom"

     

14 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    5 years ago

    Hi zaza ,

    try adjusting your statement like so:

     

     Table.Profile(Query3, {{"Custom", each Type.Is(_, type nullable number), each List.Sum(    List.Select( _, each _ = 1 )    )}})

     

     

      • ImkeF's avatar
        ImkeF
        Community Champion
        5 years ago

        Hi zaza ,

        so you want to see 4 in each column? Good to understand your requirement now.
        Then adjust it to return null for the non-Column1-rows (if [Column] = "Column1" then...) and fill down (and up, if necessary) the value for Column1.
        Having said this I'm wondering why you try to force it through the opional parameter. 
        Simply add a column after the Table.Profile step with the following formula: 

         

        List.Count(List.Select( Query3[Column1], (inner) => inner = 1 ))

         

         

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello zaza 

     

    probably the column type is not okay.

    Try to add this step before your Table.Profile

    Transform = Table.TransformColumns(PreviousStep,{{"Column1", each Number.From(_), type number}})

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

  • PDJsh's avatar
    PDJsh
    Regular Visitor
    3 years ago

    Hi, 

     

    I have another similar issue.

    I have two columns,

    column 1 is a month column(which also includes a row called annual),

    column 2 are values

    i want to calculate the sum of values from July to June excluding annual.

     

    Could u please help?