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"

     

zaza's avatar
zaza
Resolver III
6 years ago

Removed

ImkeF's avatar
ImkeF
Community Champion
6 years ago

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/

 

  • zaza's avatar
    zaza
    Resolver III
    6 years ago

    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's avatar
    ImkeF
    Community Champion
    6 years ago

    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.

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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"