Forum Discussion
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...
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"
Here is the excel with the formulas, I think this will clear it up.
https://drive.google.com/file/d/1f2WhD5CVaamnnYzvs2AKMFZiAW0PBnqR/view?usp=sharing
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"