Forum Discussion

zaza's avatar
zaza
Resolver III
6 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 prof...
  • ImkeF's avatar
    ImkeF
    6 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"

     

ImkeF's avatar
ImkeF
Community Champion
6 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 )    )}})

 

 

zaza's avatar
zaza
Resolver III
6 years ago

Removed

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 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 ))

     

     

    • zaza's avatar
      zaza
      Resolver III
      6 years ago

      Removed

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        Hi zaza ,

        good to know what the task is now. Would be great if you had provided this at the beginning.

         

        You can add a column to your table with the simple Table.Profile command like so:

        Table.AddColumn(TableProfileStep, "Custom2", each List.Sum(Table.Column(Table.SelectRows( Query3, (inner)=> inner[Column1] = 1 ), [Column])))

         

        I don't see how you could achieve this with the 2nd parameter within the Table.Profile-function.