Forum Discussion
Table.Profile sum based on another column
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"
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.
Removed
No problem.
The environment of the 2nd argument doesn't allow to reference the columns that the Table.Profile column generates. So there is now way (that I know at least) to determine the relevant column. Therefore you have to execute the Table.Profile function first for being able to reference the [Column]-column as identifier.If you want to learn more about the environment concept in M, I can recommend the article series that I've written with Lars here: https://ssbi-blog.de/blog/technical-topics-english/the-environment-concept-in-m-for-power-query-and-power-bi-desktop-part-1/
I apologise for the confusion, I made a mistake in my original post and the follow up post as well and ended up asking for something totally different. I modified my original query instead of posting a new one. I hope now it's prefectly clear.