Table.Profile sum based on another column

[zaza]

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

[PDJsh]

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?

[Jimmy801]

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

[ImkeF]

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]

Removed

[ImkeF]

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]

Removed

[ImkeF]

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.

[zaza]

Removed

[ImkeF]

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-...

[zaza]

@ImkeF 

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.

[ImkeF]

Hi @zaza ,

I have no clue how to end up with 42 or 32 based on the sample data in the image you've given.
Please create an Excel-Workbook with sample data before and after, upload it to a clouds storage and paste the link to it here.

[Anonymous]

if you don't have other specific reasons to  use table.profile, you can do the job without in this way, for exmple:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyBGEDAwOlWJ1oCAdFALcSfPIGBpiGQMTQ1eBQYICwJRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type 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}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "SumCol11whereEq1", each List.Accumulate(List.PositionOf(Record.FieldValues(_),1,Occurrence.All),0,(s,c)=>s+Record.FieldValues(#"Transposed Table"{0}){c}))
in
    #"Added Custom"

[zaza]

@ImkeF 

Here is the excel with the formulas, I think this will clear it up.

https://drive.google.com/file/d/1f2WhD5CVaamnnYzvs2AKMFZiAW0PBnqR/view?usp=sharing

[ImkeF · Accepted solution]

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"