Powerquery to create a new column with the following features

[Anonymous]

Hi All, I was hoping someone could help with this column I am trying to create using power query

I have the following table:

Classification	Company	Value	(column needed)
E	Apple	10	60
E	Tesla	15	55
E	Apple	50	60
E	Tesla	40	55
S	Apple	20	30
S	Apple	10	30
S	Tesla	5	5

Basically I nede a column (on power query) that does the sum of the value for each classification and company and repeats these values in the cells wherever the company name comes up.

For example Apple has to E's, valued 10 and 50. so I want my column to say 60 for both these fields.

The DAX code for this is as follows: Column = CALCULATE(sum(Table1[Value]),ALLEXCEPT(Table1,Table1[Classification],Table1[Company]))


Looking for powerquery code so that I can unpivot this column

[AlB · Accepted solution]

Hi @Anonymous 

Paste the following code in a blank (empty) query so that you can see the steps. The relevant one is the last one (#"Added Custom")

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclXSUXIsKMhJBdKGBkqxOhChkNTinESQkClcCKbKFFOVCUQoGEmVEaaQIUIIphFoeiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Classification = _t, Company = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "New column", each List.Sum(Table.SelectRows(#"Changed Type", (inner)=> inner[Classification] = [Classification] and inner[Company]=[Company])[Value]))
in
    #"Added Custom"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers